Project Schedule Health from PMO Template

Here is a revised version that will have activities that start in the next 10 days turn green:

=IF([% Complete]@row = 1, "Blue", IF([End Date]@row < TODAY(), "Red", IF(AND([Start Date]@row <= TODAY(), ABS(NETWORKDAYS(TODAY(), [Start Date]@row) + 1) / ABS(NETWORKDAYS([Start Date]@row, [End Date]@row)) > [% Complete]@row), "Yellow", IF(AND([Start Date]@row <= TODAY(), ABS(NETWORKDAYS(TODAY(), [Start Date]@row) + 1) / ABS(NETWORKDAYS([Start Date]@row, [End Date]@row)) <= [% Complete]@row), "Green", IF(AND([Start Date]@row > TODAY(), [Start Date]@row <= WORKDAY(TODAY(), 10)), "Green", "")))))

For best practices, please reference Advanced Formula Examples sheet in the Smartsheet Formula Examples Template (Smartsheet Formula Examples Template | Smartsheet).

All comments

Ipshita

Hi @Marcia562

Hope you're doing well. Let me try to help you with this - I have created a test scenario for you here -

Your formula above is not correct, if you want the criterion of both Delta and %complete columns, then you have to use the IF statement twice, where the value for true is blue when delta is 0 and task is 100% complete and value for false is red. Accordingly, to add more symbol colors, just keep replicating the IF statement.

Hope this helps,

Cheers!

Ipshita

Hi @Marcia562 ,

I converted your schedule health parameters into the following formulas. I also included a screenshot demonstrating the different health conditions.

Schedule Health:

= IF(AND([Schedule Delta (%)]@row = 0, [% Complete]@row = 1), "Blue", IF(AND([Schedule Delta (%)]@row > 0.1, NETWORKDAYS(TODAY(), [Target End Date]@row) >= 10), "Green", IF(AND([Schedule Delta (%)]@row <= 0.1, NETWORKDAYS(TODAY(), [Target End Date]@row) >= 10), "Yellow", IF(AND([Schedule Delta (%)]@row > 0.1, NETWORKDAYS(TODAY(), [Target End Date]@row) < 1), "Red", ""))))

Status:

=IF([Schedule Health]@row = "Blue", "Complete", IF([Schedule Health]@row = "Green", "In Progress", IF([Schedule Health]@row = "Yellow", "At Risk", IF([Schedule Health]@row = "Red", "Late", ""))))

Screenshot:

If you don't want to use the target dates for health, here is a formula that I use for schedule health:

=IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(AND([Start Date]@row < TODAY() + 5, ABS(NETDAYS(TODAY(), [Start Date]@row)) / ABS(NETDAYS([Start Date]@row, [End Date]@row)) > [% Complete]@row), "Yellow", IF(AND([% Complete]@row = 0, [Start Date]@row > TODAY() + 5), "", "Green"))))

Let me know if you have any questions!

Best,

Brandon

@breso Thank you for your feedback. I'm running across an error. R16 & R17 schedule health/status are showing up blank and I can't figure out why.

Also, not sure why R18-R20 are showing up yellow/at risk given that the date it's not 10 days from today's date.

Schedule Help_03.jpg

R16 & R17 do not show up because the Schedule Delta (%) is 0%. This is also why R18-20 display as Yellow rather than Green.

The parameters in your original post require a schedule delta value >10% for Green or Red to result.

Blue (complete) = if schedule delta is = 0% AND % complete is 100%
Green (on track) = today's date is 10 days b4 target end date AND if schedule delta % is > 10%
Yellow (at risk) = today's date is 10 days b4 target end date AND if schedule delta % is < 10%
Red (past due) = today's date is 1 day after target end date AND schedule delta % is > 10%

What formula are you using for Schedule Delta (%)? You could try different parameters based on today's date and % complete. Such as:

Blue (Complete): [% Complete] = 100%
Red (Late): Today's Date > [End Date] AND [% Complete] < 100%
Yellow (At Risk): Today's Date > [Start Date] AND [% Complete] < ((TODAY() - [Start Date]) / [Duration])
Green (On Track): Today's Date <= [Start Date] OR [% Complete] >= ((TODAY() - [Start Date]) / [Duration])

@breso duhhh 🤦‍♀️ thanks for pointing that out. Schedule delta formula attached (_04). Shouldn't image _05 show red based on #2 above (Red (Late): Today's Date > [End Date] AND [% Complete] < 100%)?

Schedule Help_04.jpg

No worries! Also, I did not see an image_05. What formula are you now using for schedule health?

@breso attached is formula for schedule health.

Schedule Help_06.jpg

Use column formulas to apply calculations to all rows in a sheet | Smartsheet Learning Center

Thank you, @Marcia562. It looks like the formula is only being used in specific rows. Could you convert it from a Cell Formula to a Column Formula?

@breso how were you able to tell it wasn't converted?

@Marcia562, the column header was missing the function icon.

@breso fixed (_07). Just curious what is the best best practice regarding the tasks that haven't started yet? Should the health be green or the cell left blank? Currently they are all yellow (_07).

Schedule Help_07.jpg

Is it possible to make the status start off as not started until I indicate the %complete (_08)?

Schedule Help_08.jpg

Here is an updated Status formula:

=IF([Schedule Health 1]@row = "Blue", "Complete", IF([Schedule Health 1]@row = "Green", "In Progress", IF([Schedule Health 1]@row = "Yellow", "At Risk", IF([Schedule Health 1]@row = "Red", "Late", IF([Schedule Health 1]@row = "", "Not Started", "")))))

Your tasks are yellow because the activities are at least 10 days out and schedule delta is 0% per your formula:

Yellow = [Schedule Delta (%)]@row <= 0.1 AND [Target End Date] - TODAY() >= 10

The best practice for Smartsheet tasks that haven't started yet is to have the schedule health indicator for that row be green, which indicates that the task is on track to start as planned. This provides a clear and accurate status update for the task without causing confusion or misinterpretation.

Using a blue schedule health indicator would suggest that the task is already complete, which is inaccurate. A red schedule health indicator would suggest that the task is late, which is also incorrect as the task has not yet started. Using a yellow schedule health indicator could suggest that the task is off track or in danger of being delayed, which may cause unnecessary concern or confusion. Finally, leaving the schedule health indicator blank could suggest that the task has not yet been considered or assigned, which is not a helpful status update.

@breso makes sense, Thx.

I updated the status formula but received a syntax error. Also, how do you get the formula to be in that area where I'm able to scroll and grab the formula?

Thank you for helping me with these formula's as this is NOT my forte🤪. What would your recommendation be fpr the following: I want to be able to have both the health and status update when I update the %complete. AND I also want to track the project based on the 'target end date" in order to show the team whether the task/project is tracking to what we originally indicated. So perhaps my original ask below isn't laid out correctly.

Blue (complete) = if schedule delta is = 0% AND % complete is 100%
Green (on track) = today's date is 10 days b4 target end date AND if schedule delta % is > 10%
Yellow (at risk) = today's date is 10 days b4 target end date AND if schedule delta % is < 10%
Red (past due) = today's date is 1 day after target end date AND schedule delta % is > 10%

My goal is to end up using this as my template in order to create dashboards for our leadership team.

Hi @Marcia562, the column names in the formula may not match the names in your sheet. You probably need to delete the " 1" from the column names in the formula.

In your Sheet you need to right click the cell and select edit column formula. In the community, you can toggle different formats:

You could add an OR argument to each status with a % complete requirement