Need help with formulas using health status and dates

Allison Amato

Hi,

I'm trying to do a formula in the "Health" column. Once we are within 30 days from the due date, the status turns "Yellow" and once it is within 7 days of the due date and past it, the status would be "Red". Any other status would be green. I'm at a lost to where even to begin even after viewing some of the videos. If you've seen this before or have experience can you point me in the right direction?

Thanks,

Allison

Kelly Moore

Hi Allison,

I made an assumption that your sheet also had a column (which I called Status) that would indicate if the task was Complete. Make sure you update this formula to your actual column name and edit my word 'Completed' to your actual wording. Note the wording is case sensitive.

Add this formula to your Health column. The ball will turn green if the Status is Complete, or if the Due Date is 30 d or more greater than Today. The ball will turn Red if the Due Date is within 7 d or less than today. Anything in between will turn to yellow.

=IF(OR(Status@row = "Completed", [Due Date]@row - TODAY() >= 30), "Green", IF([Due Date]@row - TODAY() <= 7, "Red", "Yellow"))

I hope this helps

Kelly