Forum Discussion

SandraleeOster's avatar
SandraleeOster
Qrew Trainee
2 years ago

If statement for future and overdue dates

I want to color code based on due date. Green = On Track (11 + days in future) Yellow = Due Date is within 10 days Red = Overdue is after the due date. I was able to get my formula for green a...
Formulas and functions
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion
Can you post your current formula? Then we can correct it.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
SandraleeOster's avatar
SandraleeOster
Qrew Trainee
2 years ago
Hi Mark,

Here is the formula:

If(
IsNull( [Date Case Closed]) and Today() <= [Investigation Due Date],"<img src=\"https://images.quickbase.com/si/16/222-point_green.png\" title=\"On Time\">",

IsNull( [Date Case Closed]) and Today() <= [Investigation Due Date] + Days(10), "<img src=\"https://images.quickbase.com/si/16/223-point_yellow.png\" title=\"DueSoon\">",

IsNull( [Date Case Closed]) and Today() > [Investigation Due Date],"<img src=\"https://images.quickbase.com/si/16/221-point_red.png\"title=\"OverDue\">"
)

Thank you for your help!

------------------------------
Sandralee Oster
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    2 years ago
    When you have an IF, it walks down each test in sequence and takes the first true condition it finds. 

    So I think you need to check in this sequence.  But for my own mental benefit I'm gong to flip some of your signs.  



    If(

    IsNull( [Date Case Closed]) and [Investigation Due Date] < Today(),
    "<img src=\"https://images.quickbase.com/si/16/221-point_red.png\"title=\"OverDue\">",


    IsNull( [Date Case Closed]) and [Investigation Due Date] <= Today() + Days(10), "<img src=\"https://images.quickbase.com/si/16/223-point_yellow.png\" title=\"DueSoon\">",

    IsNull( [Date Case Closed]) and [Investigation Due Date] > Today() + Days(10)<= ,"<img src=\"https://images.quickbase.com/si/16/222-point_green.png\" title=\"On Time\">"

    )

    I think that will work. You will need to check the logic for what you want to happen when the investigation due date is exactly 10 days away .. should that be yellow  or green?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • SandraleeOster's avatar
      SandraleeOster
      Qrew Trainee
      2 years ago
      Thank you, Mark!  I tweaked the on time formula and it all works now!

      ------------------------------
      Sandralee Oster
      ------------------------------