Skip to main content
Question

Color-Coding Cells based on Dates AND Multi-Choice

MeaganMcOlin
Forum|alt.badge.img+7

I have a multi-choice field that you can do one of three things with. 1) Leave it blank 2) Select "Missed" 3) Select "Scheduled"

I want the field next to it to color-code base on the info. 

1) If left blank I want the cell to turn red if a date is past and yellow if the date is upcoming

2) MIssed should be gray

3) Scheduled should be green

I already have a formula that almost does what, except originally we have a check box that was either checked or not, and now we have a multi-choice. COuld someone help me update this with language for the multi-choice field? 

Here is the old code: 

var text red = "<a style=\" text-decoration: none; background: #ea4335; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">"&ToText(ToDays(Today()-[Appointment Needed By]))&" Days Overdue</a>";

var text green = "<a style=\" text-decoration: none; background: #34a853; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">Complete</a>";

var text yellow = "<a style=\" text-decoration: none; background: #fbbc05; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">"&ToDays(Today()-[Appointment Needed By])&" Days Due</a>";

If([Scheduled - Yes]=true,$green,
If([Scheduled - Yes]=false and [Appointment Needed By] >= Today(),$yellow,
If([Scheduled - Yes]=false and [Appointment Needed By] < Today(),$red)))

____________

EXAMPLE: Marie Lee should be Gray "Missed"



------------------------------
Meagan McOlin
------------------------------

This topic has been closed for replies.

  • AustinK
  • Registered
  • August 23, 2021
Your formula works from the top down. Once it finds the first thing in that formula that matches it ignores the rest of it. 

If(
[Scheduled - Yes] = "Scheduled" ,$green,
[Scheduled - Yes] = "Missed" ,$gray,
[Scheduled - Yes] = "" and [Appointment Needed By] >= Today(),$yellow,
[Scheduled - Yes] = "" and [Appointment Needed By] < Today(),$red
)

This would color anything with Scheduled selected green, ignoring any other thing on the record. Gray if Missed is selected, and then if blank would color either yellow or red. Let me know if it does not work as anticipated but I think it should. 

Although now that I look at your post you have given your old formula so this may not help you at all since you are asking how to update a new formula that is not here.
    MeaganMcOlin
    Forum|alt.badge.img+7

    If anyone is interested I found a code that works for me.

    Old (checkbox version):

    If([Scheduled - Yes]=true,$green,
    If([Scheduled - Yes]=false and [Appointment Needed By] >= Today(),$yellow,
    If([Scheduled - Yes]=false and [Appointment Needed By] < Today(),$red)))

    New (multi choice version):

    If([Scheduled - Yes]="Scheduled", $green,
    If(([Scheduled - Yes]="Missed"), $gray,
    If(Contains([Scheduled - Yes],"Unscheduled") and [Appointment Needed By] >= Today(),$yellow,
    If(Contains([Scheduled - Yes],"Unscheduled") and [Appointment Needed By] < Today(),$red))))

    Thanks!



    ------------------------------
    Meagan McOlin
    ------------------------------
    Terms & ConditionsAccessibility statement