How to set up schedule health for my project plan in Smartsheet?

katarinapetrovic

Hello! I started using Smartsheet for project management and I'm having a hard time setting up schedule health. It is a bit time-sensitive as well. This is what I need:

I need some sort of formula that will automatically show schedule health based on the completion or non-completion of a task, using the Start Date, End Date, and Status columns. These are the criteria:

Green - on schedule (started on or before the start date/ended on or before the end date)

Yellow (2 work days passed from start date but it wasn't started/2 work days passed from end date but it wasn't ended)

Red (5 work days passed from start date but it wasn't started/5 work days passed from end date but it wasn't ended)

Gray (status is Not Started, Cancelled or On Hold)

Something along these lines. Could someone please help with this? Also note that if possible we will not use schedule delta.

kowal

yes it can but in symboles thare not so many colours.

You can max have 4 colours so RYG + Blue or RYG + Gray.

However this formula shall work for you:

=IF([Status]@row = "Complete", "Blue", IF(OR([Status]@row = "Not Started", [Status]@row = "Cancelled"), "Grey", IF([Status]@row = "In Progress", "Green", IF([End Date]@row - TODAY() = 2, "Yellow", IF([End Date]@row - TODAY() = -2, "Red", "")))))

Do you also keep the start date column in your sheet?

Paul Newcome

If a status is Complete, do we not care if it was completed late? Just make it green? Or do you need to show it as red if it was (for example) completed but 10 days late?

kowal

hi @katarinapetrovic,

but how do you know if project started on the date or not? I think health is calculated more on the status of the Task comparing to the end date like: if Start Date is in 2 days and tsk is not complete it should be red, if tasks end date is in 7 days and it's not complete it should be yellow otherwise is shall be green.

you need to use the nested if formula.

Something like:

=if(OR(Status@row="Not Started", Status@row="On Hold", Status@row="Cancelled"), "Gray", - this will make the status gray if any of this criteria in status is met.

if(AND(End Date]@row - 2 < TODAY(), Status@row<>"Complete"),"Red", IF(AND([End Date]@row - 7 < TODAY(), status@row<>"Complete"), "Yellow", "Green"))) - this is based on the end date so if end date is in 2 days it will be red if in 7 days it will be orange…

katarinapetrovic

Hi @kowal and @Paul Newcome apologies - I had a talk with my manager :) This is what we are going with:

-Blue (task Complete)

-Grey (task Not Started or Cancelled)

-Green (task In Progress and on schedule)

-Yellow (starts 2 days before End Date)

-Red (starts 2 days after End Date)

Could this be translated into a formula? Thank you so much for helping!

kowal

yes it can but in symboles thare not so many colours.

You can max have 4 colours so RYG + Blue or RYG + Gray.

However this formula shall work for you:

=IF([Status]@row = "Complete", "Blue", IF(OR([Status]@row = "Not Started", [Status]@row = "Cancelled"), "Grey", IF([Status]@row = "In Progress", "Green", IF([End Date]@row - TODAY() = 2, "Yellow", IF([End Date]@row - TODAY() = -2, "Red", "")))))

Do you also keep the start date column in your sheet?

katarinapetrovic

Hi @kowal thank you so much!! I will try this formula out :) Yes, I also have a Start Date column, does that change the formula? Thank you!!

Edit: I just tried it and the yellow and red functions are sadly not functional.

kowal

hi @katarinapetrovic,

well not in the formula that I sent to you the problem is what does it mean that something starts 2 days after end date or 2 days before end date?

-Yellow (starts 2 days before End Date)

-Red (starts 2 days after End Date)

What starts? how shall smartsheet know what is starting before End date - maybe you meant sth more like: yellow if end date is to finish in two days and the status task is still not complete?

Red if the end date is like 2 days ago and the task of status is still not complete?

only knowing this you can setup the formula for health.

katarinapetrovic

Hi @kowal Thanks for explaining, I meant that ''yellow'' starts 2 days before End Date and ''red'' starts 2 days after the end date, but you explained it better - that is exactly what I need. :)

kowal

in that case this one is more appropriate:

=IF(Status@row = "Complete", "Blue", IF(OR(Status@row = "Not Started", Status@row = "Cancelled"), "Grey", IF(AND(Status@row = "In Progress", [End Date]@row - [Start Date]@row > 2), "Green", IF([End Date]@row - [Start Date]@row = 2, "Yellow", IF([Start Date]@row - [End Date]@row < 2, "Red", "")))))