Overdue formula and health status

clairemorse09

Hi there,

I need a little help please, I am trying to get two formulas for the following:

Formula 1

I need my status of a task to reflect the proper status based on the percentage completed and the due date of the tasks. My status options are:

Not started (this is for something that is on 0%, but isnt past the due date)

Not started - Overdue (this is for something that is on 0% and is past the due date)

In Progress - On Track (this is for something that is not at 0% and isnt past the due date)

In Progress - Overdue (this is for something that is not at 0% and is past the due date)

Completed (Percentage complete is 100%)

Formula 2

Then once the formula above is working. I then want the health ball to reflect the status of the task, colours I would need are below:

Not started - Grey

Not started - Overdue - Red

In Progress - On Track - Yellow

In Progress - Overdue - Red

Completed - Green

Please help me!

Nick Korna

Hi @clairemorse09,

For Formula 1:

=IF([% complete]@row = 1, "Completed", IF(AND([% complete]@row = 0, [Due Date]@row > TODAY()), "Not started", IF(AND([% complete]@row = 0, [Due Date]@row < TODAY()), "Not Started - Overdue", IF(AND([% complete]@row > 0, [Due Date]@row > TODAY()), "In Progress - On Track", IF(AND([% complete]@row > 0, [Due Date]@row < TODAY()), "In Progress - Overdue")))))

For Formula 2:

=IF([Formula 1]@row = "Not Started", "Gray", IF(OR([Formula 1]@row = "Not Started - Overdue", [Formula 1]@row = "In Progress - Overdue"), "Red", IF([Formula 1]@row = "In Progress - On Track", "Yellow", IF([Formula 1]@row = "Completed", "Green"))))

Sample:

Hope this helps, but if you've any problems/questions then just post! 🙂