Help with formula: if Status is X and End Date is in X days, change symbol to X

Kadie M

Hello - I am looking for help with a formula that states:

If "Status" is not "Complete" and the "End Date" is 5 biz days or less from today - change symbol to Red

If "Status" is not "Complete" and the "End Date" is within 6-10 biz days from today - change symbol to yellow

If "Status" is "Complete" OR the "End Date" is 11+ biz days from today - change symbol to green

See # of days until the due date calculated below. Given the desired logic above, the item due in 6 days should be yellow and the item due in 11 days should be green.

Days From Today Until Due =NETWORKDAYS(TODAY(1), [End Date]@row)

This is the formula used for Health:

=IF(AND(Status@row <> "Complete", [End Date]@row - 5 <= TODAY(5)), "Red", IF(AND(Status@row <> "Complete", [End Date]@row - 10 <= TODAY(10)), "Yellow", IF(OR(Status@row = "Complete", [End Date]@row - 11 >= TODAY(11)), "Green")))

Thanks!!

Kadie M

@Darren Mullen - That worked perfectly! Thank you!

Full formula for reference:

=IF(AND(Status@row <> "Complete", ([End Date]@row - TODAY()) <= 5, ([End Date]@row - TODAY()) >= 0), "Red", IF(AND(Status@row <> "Complete", ([End Date]@row - TODAY()) <= 10, ([End Date]@row - TODAY()) >= 6), "Yellow", IF(OR(Status@row = "Complete", ([End Date]@row - TODAY()) >= 11), "Green")))

Darren Mullen

@Kadie M You're making a lot of adjustments to the dates by subtracting from the End Date as well as adding days to Today's date.

It would be more straightforward if you did something like this:

=IF(AND(Status@row <> "Complete", ([End Date]@row - Today()) <= 5, ([End Date]@row - Today()) >= 0), "Red" ..............

Kadie M

@Darren Mullen - That worked perfectly! Thank you!

Full formula for reference:

=IF(AND(Status@row <> "Complete", ([End Date]@row - TODAY()) <= 5, ([End Date]@row - TODAY()) >= 0), "Red", IF(AND(Status@row <> "Complete", ([End Date]@row - TODAY()) <= 10, ([End Date]@row - TODAY()) >= 6), "Yellow", IF(OR(Status@row = "Complete", ([End Date]@row - TODAY()) >= 11), "Green")))

Darren Mullen

@Kadie M You're welcome, however I realized I lead you a little astray. You probably don't want this condition in there ([End Date]@row - TODAY()) >= 0)

If you leave that in, then it won't turn red when the end date is greater than today!

Sorry, I wasn't thinking it all the way through yesterday.

Kadie M

@Darren Mullen - You are right and no worries at all! I removed that piece and it's working perfectly. Thanks again for your help!!

=IF(AND(Status@row <> "Complete", ([End Date]@row - TODAY()) <= 5), "Red", IF(AND(Status@row <> "Complete", ([End Date]@row - TODAY()) <= 10, ([End Date]@row - TODAY()) >= 6), "Yellow", IF(OR(Status@row = "Complete", ([End Date]@row - TODAY()) >= 11), "Green")))