Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Conditional formatting if task is behind schedule

Roy Janssen
edited 12/09/19 in Archived 2016 Posts

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



    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.





  • Etienne Desbiens
    edited 07/05/16

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

  • 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?




  • J. Craig Williams
    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.




This discussion has been closed.