Color-Coding Cells based on Dates AND Multi-Choice
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
------------------------------