Conditional formatting if task is behind schedule

Roy Janssen

Hi guys,

 

I'm new to Smartsheet so forgive me to ask the question.

 

How do I setup conditional formatting to a task that is behind schedule? I created 2 lines of conditional formatting already:

1 ) task is 100% complete -> green row

2) end date is in the past and % complete is not 100% -> red row

 

I'd like to add a third (yellow) formatting for a task which is currently ongoing (so current date is between start and end date), but where the progress is less than where it should be today. Can you guys help me out?

Etienne Desbiens

Hi!

 

I'd simply put those conditions :

Task% is not 100% complete

Start date is in the past

End date is in the future

 

You say you want the to see if the progress is less than where it should be today... How do you calculate the progress prevision? Is it simply somthing like :
=(Today()-StartDate)/(Netdays(StartDate, EndDate))*100
If so and if you want to integrate that to your formatting, I would add this condition :

Task% is smaller than prevision

 

Hope it helps!

 

Étienne Desbiens

Kennedy Stomps

Great suggestion, Etienne! This is also outlined in a little more detail in this article from our Help Center: https://www.smartsheet.com/blog/smartsheet-tip-using-conditional-formatting-and-formulas-together. Let me know if you run into any trouble implementing this in your sheets!

 

 

Roy Janssen

You are amazing. Only thing I worked around was "If so and if you want to integrate that to your formatting, I would add this condition : Task% is smaller than prevision".

 

I wasn't able to get it done in  the conditional formatting so created another column to calculate expectedprogress-% Complete and based my conditional formatting on that one.

 

Roy

 

 

Etienne Desbiens

I also used the color formatting at the beginning of my project but I realised that using color indicators was more effective (in a way where you can easily move them to other sheets, change the parameters in a fraction of a second and use them to calculate data or to build reports).

 

I don't know if it could apply to your project but here's an example that could work for you (I use green for on time stuff, yellow for things that are behind schedule, red for late and blue for done.):

 

=IF(Task%4 = 100, "Blue", IF([End date]4 < TODAY(), "Red", IF(Task%4 < (((TODAY() - [Start date]4) / (NETDAYS([Start date]4, [End date]4))) * 100), "Yellow", "Green")))

 

No need to have an expected progression collumn on that one since everything is included in the formula. Hope that helps you!

Étienne Desbiens

Roy Janssen

Nice solution also, thank you. It definately prevents the chart from becoming a coloring book. 

Sarah Downes

HI all,

Appreciate this is an older thread but am having difficulty and wondered if there was help! I've tried a few of the conditional formatting formulae so far to try and do what is explained above (charting progress against dates / expected % complete) and all formulae action the date part without the % part working. eg =IF([% Complete]17 = 100, "Blue", IF(Finish17 < TODAY(), "Red", IF([% Complete]17 < (((TODAY() - Start17) / (NETDAYS(Start17, Finish17))) * 100), "Yellow", "Green")))

Any clues?

 

Thanks,

Sarah

J. Craig Williams

[% Complete] is likely 0-1 not 0-100%

You enter 95% but the value is 0.95

I think this may give you what you are looking for

=IF([% Complete]17 = 1, "Blue", IF(Finish17 < TODAY(), "Red", IF([% Complete]17 < ((TODAY() - Start17) / (NETDAYS(Start17, Finish17))), "Yellow", "Green")))

Interesting "Yellow" determination there.

Craig