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

Options

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!!

Best Answer

  • Kadie M
    Kadie M ✭✭
    Answer ✓
    Options

    @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")))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!