Switc code from date to checkbox

Question

Hello,&nbsp;
I currently have this wonderful code that color codes cells based on the due date and the actual date. However, we are now chaging this to be a due date and a checkbox if the appointment was scheduled. I would like cells to be color coded based on the following.&nbsp;
Green - Checkbox checked (due date any time)
Yellow - Due date upcoming (and the checkbox isn't checked)
Red - Due date is in the past (and the checkbox isn't checked)
Could someone please help me modifiy this code?&nbsp;var text red = "&lt;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; \"&gt;"&amp;ToText(ToDays(Today()-[Appointment Needed By]))&amp;" Days Overdue&lt;/a&gt;";var text green = "&lt;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; \"&gt;Complete&lt;/a&gt;";var text yellow = "&lt;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; \"&gt;"&amp;ToDays(Today()-[Appointment Needed By])&amp;" Days Due&lt;/a&gt;";If(not(IsNull([Scheduled - Yes])),$green,If(IsNull([Scheduled - Yes]) and [Appointment Needed By] &gt;= Today(),$yellow,If(IsNull([Scheduled - Yes]) and [Appointment Needed By] &lt; Today(),$red)))THANK YOU!
------------------------------Meagan McOlin------------------------------

donlarson · Answer

Meagin,It looks like a simple change from what you have posted:If(not(IsNull([Scheduled - Yes])) and [Checkbox]=false,&nbsp;$green,&nbsp; .....In this example&nbsp; [Checkbox] is the field that your users have set.------------------------------Don LarsonPaasporterWestlake OH------------------------------

meaganmcolin · Answer

Don,

Thank you so much for your response. I replaced the field with [Checkbox] but it says that the field is not identified.&nbsp;

Here is a snip of the table itself.&nbsp;
Any assistance you could provide would be greatly appreciated.&nbsp;
Thank you!------------------------------Meagan McOlin------------------------------

donlarson · Answer

Use the name of the actual checkbox field. For example if your field is called

[Due Date Set]

Then that is what goes into your formula.

It could just as easily be any other name that has meaning to your users.

------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------

meaganmcolin · Answer

Don,

That is what I tried orginally, but I get the error below.&nbsp;

Here is the full code again if you need it:&nbsp;
var text red = "&lt;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; \"&gt;"&amp;ToText(ToDays(Today()-[Appointment Needed By]))&amp;" Days Overdue&lt;/a&gt;";var text green = "&lt;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; \"&gt;Complete&lt;/a&gt;";var text yellow = "&lt;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; \"&gt;"&amp;ToDays(Today()-[Appointment Needed By])&amp;" Days Due&lt;/a&gt;";If(not(IsNull([Scheduled - Yes])),$green,If(IsNull([Scheduled - Yes]) and [Appointment Needed By] &gt;= Today(),$yellow,If(IsNull([Scheduled - Yes]]) and [Appointment Needed By] &lt; Today(),$red)))

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

meaganmcolin · Answer

Don,
Thank you for your continued help. I fixed the color-coding issue (I needed a true/false statement instead of Null) but now I am trying to code it so that the field turns yellow if the "Appointment Needed By" is coming up in 2 weeks or less. Right now it turns yellow if the date is any date less than today. We don't need it to be yellow all the time though, just when the "Appointment Needed By" date is drawing near.&nbsp;
------------------------------Meagan McOlin------------------------------

Forum Discussion

Switc code from date to checkbox

5 Replies