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...
Forum Discussion
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
------------------------------
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
Qrew Legend
Qrew LegendWhen 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
------------------------------
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
------------------------------
- Thank you, Mark! I tweaked the on time formula and it all works now!
------------------------------
Sandralee Oster
------------------------------