Forum Discussion

SandraleeOster's avatar
SandraleeOster
Qrew Trainee
3 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 and red to work but my yellow is not working.  If it is supposed to be yellow, it is actually turning green instead.

Any help would be greatly appreciated,

Thanks,
Sandy

------------------------------
Sandralee Oster
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    Can you post your current formula? Then we can correct it.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • SandraleeOster's avatar
      SandraleeOster
      Qrew Trainee
      3 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
        Icon for Qrew Legend rankQrew Legend
        3 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
        ------------------------------