Forum Discussion

MeaganMcOlin's avatar
MeaganMcOlin
Qrew Cadet
4 years ago

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

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
------------------------------
Formulas and functions
  • QuickBaseJunkie's avatar
    QuickBaseJunkie
    Qrew Commander
    4 years ago
    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
    ------------------------------