Overdue formula and health status

clairemorse09
clairemorse09 ✭✭
edited 10/17/23 in Smartsheet Basics

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!

Answers

  • Nick Korna
    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! 🙂