Need to Upgrade my Task Health Formula!


Hi! I have a very basic formula in place to calculate task health (red or green)

I am currently using:

=IF(AND(Status@row = <>"Complete", [Due Date]@row <= TODAY()), "Red", "Green")

I have two questions here.

1) I need this formula to also include "done" with "complete" IE... If the status is not "complete" OR "done" and the due date is less than today, task health = RED. (I have to use both DONE and COMPLETE, as my tasks that are synced to JIRA have a status of DONE **per JIRA** and all other tasks are marked as COMPLETE across all company projects (so I don't want to try and switch the wording to DONE for those).

2) Can I beef this formula up more? I would love for some tasks to populate YELLOW if the due date is in the next 7 days and the status is not "complete" or "done"

Thanks for your help!! 😀

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!