Need to Upgrade my Task Health Formula!

Hannah Gray

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

Paul Newcome

Give this a go...

=IF(OR(Status@row = "Complete", Status@row = "Done"), "Green", IF([Due Date]@row<= TODAY(), "Red", IF([Due Date]@row<= TODAY(7), "Yellow")))

Paul Newcome

Give this a go...

=IF(OR(Status@row = "Complete", Status@row = "Done"), "Green", IF([Due Date]@row<= TODAY(), "Red", IF([Due Date]@row<= TODAY(7), "Yellow")))

Hannah Gray

Thank you Paul! This worked for me.

Paul Newcome

Happy to help. 👍️