Health Status Formula dependent on due date

Ali Hoyt

I am experiencing trouble trying to write a formula for the below situation. See above screenshot as reference. Can someone please advise on how to write this formula correctly?

This is the current formula I'm using, but I know it's not correct:

=IF[On Hold] = 1, "Gray", IF([Due Date]@row >= TODAY(15), "Green", IF(AND([Due Date]@row >= TODAY(3), [Due Date]1 <= TODAY(14)), "Yellow", IF([Due Date]@row < TODAY(3), "Red")))

This is the situation I want to solve for:

• If the DUE DATE is within 3 days of TODAY or in the past, turn the health symbol "Red"
• If the DUE DATE is within 3 to 14 days from today, turn the health symbol "Yellow"
• If the DUE DATE is 15 or more days from today, turn the health symbol "Green"
• If the ON HOLD flag is checked, turn the health symbol "Gray"
• Also, if I want to turn the health symbol "Green" when I've changed the STATUS column to "Completed," how would I add that to the formula?

Please let me know if there are any questions. If there is a video or help article that explains this please let me know. I found lots of articles about health formulas dependent on % Complete, but that isn't my situation.

Thank you in advance!

Mark Cronk

Hi Ali,

Does this work for you?

=IF[On Hold] = 1, "Gray", IF(OR([Due Date]@row >= TODAY(15), Status@row="Completed"), "Green", IF([Due Date]@row < TODAY(3), "Red", "Yellow")))

Mark

Mark Cronk

Hi Ali,

Does this work for you?

=IF[On Hold] = 1, "Gray", IF(OR([Due Date]@row >= TODAY(15), Status@row="Completed"), "Green", IF([Due Date]@row < TODAY(3), "Red", "Yellow")))

Mark

Ali Hoyt

@Mark Cronk Thank you so much! Yes, that worked. I had to slightly alter it to be the following, but I kept it basically the same as your suggestion. Thank you for your help! I really was at a loss for next steps and your suggestion helped incredibly.

=IF([On Hold]@row = 1, "Gray", IF(OR([Due Date]@row >= TODAY(15), Status@row = "Complete"), "Green", IF([Due Date]@row < TODAY(3), "Red", "Yellow")))

Mark Cronk

Happy to help. Glad you found a solution. Thank you for contributing to the Community.

Mark