How do I add color coding to a calendar report based on text?

I have never been able to successfully color the background. on a calendar but if you just have one field on caleder, then you can color the text.



In these two separate examples I am making a concatenated field of the fields I want to show and then coloring the font (not the background)

Example 1


var text NameDaysReason = List(" | ", [Common Name & Initial],ToText([# Days]),[Reason]);

If([H S D]="H", "<b><font color = #A52A2A>" & $NameDaysReason & "</font color></b>", $NameDaysReason)



Example 2


var text TextBlock=

List(" | ",

"<b>" & If([Start Time]<ToTimeOfDay ("10:00 am"),"0") & ToText([Start Time]) & "</b>",

[Physician : Location : Report Type],

[Patient Name / Available]);



If(

Begins([Appointment Status],"No Show"),"<font color = red>",

Begins([Appointment Status],"Cancel"),"<font color = red>",



[Patient Name / Available]="Available","<font color = green>") & $TextBlock

Hi Mark.  I'm having trouble making this work.  I created a new field called ProjectActivity that pulls together the [Project] and [Activity] fields into one (which works using the List portion of my formula).  When I try to color code the new field using example 1 that you provided, it errors.  I copied directly from your answer but I'm guessing I'm updating something in my formula incorrectly.  Any ideas?  Thanks!

List(" - ",[Project],[Activity]);

If([Activity]="Build", "<b><font color = #00cc66>" & $ProjectActivity & "</font color></b>", $ProjectActivity)

Did you include the first line in the formula like this?


var ProjectActivity = List(" - ",[Project],[Activity]);

If([Activity]="Build", "<b><font color = #00cc66>" & $ProjectActivity & "</font color></b>", $ProjectActivity)

Yes, I have the var ProjectActivity = in the beginning of the formula.  The error I receive is: A variable declaration must be followed by a type.

Can you add in the word text to the first line?

var text ProjectActivity =  ......

When I add the word text into the first line I don't receive any errors but the field now shows on the calendar as

<b><font color = #00cc66>[Project][Activity]</font color></b>

And the font color hasn't changed.

On the field properties,  check the box that says enable HTML

I added the checkbox.  Unfortunately now I don't see any text on the calendar bar.  However, if I hover over the bar with my mouse I can see the information I want to appear on the calendar.

Sounds a bit odd. Can you try to change the color to "red" and see what it does.  ie <font color=red> with no spaces.

It does seem strange.  Below is what I have now and there's no change when I updated the line to <font color=red> with no spaces.  Is there anything else you can see that might be the issue?  I even tried creating a new calendar.  Thanks for all of your time!

var text ProjectActivity = List(" - ",[Project],[Activity]);

If([Activity]="Build", "<b><font color=red>" & $ProjectActivity & "</font color></b>", $ProjectActivity)

if you like contact me off line via my profile info and i will have a quick look.  It should work!

Thanks!

You might try using a formula field called "Activity Color":

If(

[Activity]="Build","#00FF00",

[Activity]="Go-Live","#FFC0CB",

[Activity]="Support","#00FFFF"

)

Next, make a formula field called "Activity Label":

"<div style=\"background-color:" & [Activity Color]  &";\">" & [Activity] &"</div>"


Now you can use the "Activity Label":as the display option in your calendar.

I recall helping Rachael off line.  There was a field that needed to be set to accept HTML, I think.  I don't think you can use background shading on a calendar.

I am certain that it can be done, hence my answer. Perhaps you might want to try it before you discount my response. Thanks.

The problem I had in my previous and current re-testing is for multi-day duration spans. The background shading seems to work OK for one day events, but not for multi-day.

Patrick, thanks for this solution. Two questions:
1. On my calendar the event displays with extraneous lines above and below the "Label" field: can these be eliminated?
2. Is there a way to make the "Label" field span multiple days?
Many thanks for the technique. Plenty of applications for this method.

I'm having the same problem with the extra lines above and below the field. Would love for these to be eliminated if possible.  When the report is built with a non-formula field there are no extra lines. Thanks!

here is an example of a formual to put three fields on a calendar in color

var text TextBlock=
"<b>" &
List(" | ",
[Quote - Project] ,
[Installer Name],
If([Planned Business Days to Install]>1, ToText([Planned Business Days to Install]) &  " days", ToText([Planned Business Days to Install]) & "day")
& "<br>" &List("|", [Customer Name], ToText([Related Quote])))
& "</b>";

"<span style=\"background-color:" & [Color Code]  &";\">" & $TextBlock &"</span>"

In my setup, the field for [Color code] is a lookup field that  has like  #FFF000 or whatever the hex code is for the color.

Thanks - I'm currently using the below formula in a text-formula field that is looking at a separate field (Client). I'm not sure I completely follow what you're doing. Can you see a reason why the formula below would be including the extra lines?

If(
[Client]="ABC","<div style=\"color: #000000; background-color: #C0C0C0;\">ABC</div>",
[Client]="XYZ","<div style=\"color: #ffffff; background-color: #0000FF;\">XYZ</div>",
[Client]="123","<div style=\"color: #ffffff; background-color: #FF00FF;\">123</div>")

I can only tell you an example what works.  I suspect it has to do with the difference between your  <div style> and my <span style>.

This didn't work for me. When I setup the Activity Label field, I kept getting a syntax error message. Apparently, you can't just put in the html, there has to be an If statement or some other type of expression.

I suggest that you post a new question and reference this thread in your question.  Please also post your current formula and what the error message says.

I created this and it worked for me. All you have to do is create a new TEXT Formula field. Put this formula it there. Change the field names to your fields and it should work. 

var text NameAbsenceHours = List(" | ", [Employee First Name] &" "& [Employee Last Name], [Absence],[Hours]);

If([Status]="Approved", "<b><font color = #008000>" & $NameAbsenceHours & "</font color></b>", "<b><font color = #FF0000>" & $NameAbsenceHours & "</font color></b>" )