I need a formula that will show if a task is complete, overdue, or upcoming

Question

I have a table with 2 date fields. The first field is Appointment Needed By and the second field is Appointment Scheduled. 
I need a formula that will highlight the Appointment Needed By field green if an there is a date in the Appointment Scheduled field, yellow if no appointment is scheduled and the Appointment Needed By date is coming up within 7 days, and red if we are past the Appointment Needed By date and an appointment was not scheduled. 
I have an attachment if that helps!
Thank you!------------------------------Meagan McOlin------------------------------

QuickBaseJunkie · Answer

I suggest using color-coding on the report using a formula.

This would be your formula:

If(not isnull([Appointment Scheduled]),"green",
[Appointment Needed By]<Today() and isnull([Appointment Scheduled]),"red",
[Appointment Needed By]<=(Today()+days(7)) and isnull([Appointment Scheduled]),"yellow")

Check out this video for an example: https://www.quickbasejunkie.com/blog/how-to-color-code-rows

And if you want to highlight the fields themselves, you will need to either create additional fields or add them as report fields.

Here is a video for an example of that: https://www.quickbasejunkie.com/blog/how-to-add-text-color

Let me know how it works out! 👍

------------------------------
Sharon Faust (QuickBaseJunkie.com)
Founder, Quick Base Junkie
https://quickbasejunkie.com
------------------------------

Sign up

Login to the community