Formula for Schedule Health using Status (vs. % complete)

ABPM1

Hello - I'm looking to set up a simple formula for schedule health column (using the drop down symbols of red, green, yellow, grey) that automatically populates the appropriate color based on the status column and end date.

Example: if the status is not 'complete' and the date has passed by 1-5 days, it's yellow - if passed by 5+ days it would be red.

Does anyone have an example of using status (vs. % complete) and due date for determining schedule health and can help me craft the formula? Thank you!!

Scott Peters

Hi @ABPM1 - Not knowing your exact columns, here's a first take:

=if(and(status@row <>"complete", duedate@row<=today(-10)), "red", if(and(status@row <>"complete", duedate@row<=today(-5)), "yellow", "green"))

I hope that is helpful!

ABPM1

Thank you! I'll give it a try and report back on additional questions. My column drop-down options for status are:

Not Started

In Progress

Complete

Canceled

On Hold

cbredehoeft

HI @Scott Peters, I tried this formula and it works. However, if a date is farther out the symbol disappears completely from the cell in this schedule health column. Is that expected?

Scott Peters

Hi @cbredehoeft - No, the nested IF formula is built to show you green if the due date is in the future. Are either your status or due date columns blank/null ?

cbredehoeft

Hi @Scott Peters, no, all the status are set to not started by default and target dates are populated (many in the future). Here is the formula I am using:

=IF([Actual End Date]@row = "", IF(TODAY() > [Target End Date]@row, "Red",

IF(TODAY(7) > [Target End Date]@row, "Yellow")), IF([Actual End Date]@row > [Target End Date]@row, "Red", IF(Status@row =

"Complete", "Blue", "Green")))

Scott Peters

Good morning @cbredehoeft - In your example it is about evaluation order. With nested IF formulas I find it helpful to list it out so I can make sure the overriding concept is first, and the 'if all else fails' concept is last. Try this:

=IF(Status@row = "Complete", "Blue", IF([Actual End Date]@row > [Target End Date]@row, "Red", IF(AND([Actual End Date]@row = "", TODAY() > [Target End Date]@row), "Red", IF(AND([Actual End Date]@row = "", TODAY(7) > [Target End Date]@row), "Yellow", "Green"))))

cbredehoeft

@Scott Peters this worked perfectly! Thanks so much!