# Quickbase Discussions

View Only

## If statement for future and overdue dates

• #### 1.  If statement for future and overdue dates

Posted 14 days ago
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
------------------------------

• #### 2.  RE: If statement for future and overdue dates

Posted 14 days ago
Can you post your current formula? Then we can correct it.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

• #### 3.  RE: If statement for future and overdue dates

Posted 11 days 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\">"
)

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

• #### 4.  RE: If statement for future and overdue dates

Posted 11 days 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
------------------------------

• #### 5.  RE: If statement for future and overdue dates

Posted 11 days ago
Thank you, Mark!  I tweaked the on time formula and it all works now!

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