Project Schedule Health from PMO Template

Marcia562

I've search the community and I think I'm just getting myself more confused since I'm not good with writing formula's. I downloaded the "project plan" template and I am still confused. I would like to show the following (I hope I'm explaining this right 😩):

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

Also, how would I incorporate the % complete columns in the above GYR?

This is the formula that was in the project plan template:

=IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))

I would also like the status column to update based on the color of the schedule health.

1. Blue Health = Complete
2. Green Health = In Progress
3. Yellow Health = At Risk
4. Red Health = Late

Schedule Help_01.jpg

Schedule Help_02.jpg

breso

Hi @Marcia562,

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).

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

breso

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

Marcia562

@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

breso

Hi @Marcia562,

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.

1. Blue (complete) = if schedule delta is = 0% AND % complete is 100%
2. Green (on track) = today's date is 10 days b4 target end date AND if schedule delta % is > 10%
3. Yellow (at risk) = today's date is 10 days b4 target end date AND if schedule delta % is < 10%
4. 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:

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

Marcia562

1. @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

breso

Hi @Marcia562,

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

Marcia562

@breso attached is formula for schedule health.

Schedule Help_06.jpg

breso

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?

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

Marcia562

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

breso

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

Marcia562

@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

breso

Hi @Marcia562,

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.

Marcia562

@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.

1. Blue (complete) = if schedule delta is = 0% AND % complete is 100%
2. Green (on track) = today's date is 10 days b4 target end date AND if schedule delta % is > 10%
3. Yellow (at risk) = today's date is 10 days b4 target end date AND if schedule delta % is < 10%
4. 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.

breso

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

Marcia562

@breso I used the following formula you indicated that you use for status which does not include the target date so that I can see how the status is showing up (_09). And the status looks good except the ones that haven't been started aren't showing up green. I also noticed L24 & L25 are showing at risk. I am assuming because the start date in Feb and there is not %complete noted.

=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", IF([Schedule Health]@row = "", "Not Started", "")))))

Schedule Help_09.jpg

With this formula what would be your recommendation to also track the planned vs actual date (target end date)? The only reason I have the schedule delta(%) field is because it was part of one the templates I download so I'm not married to it at all. I'm also not married to the below as this is the first time I'm using SS data that would upload to a dashboard. If you can let me know what makes more sense (best practices) then I can use that as my baseline and then edit as I go along.

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