Counting Projects that are 1 Week or More Behind Schedule

Hi Everyone,

I got a great formula from Customer Success that I thought I would share.  I wanted a way to know if a project was at least 1 week or more behind schedule.  This is what Customer Success gave me:

=COUNTIFS(Finish:Finish, TODAY() <= TODAY(-7), [% Complete]:[% Complete], <>1)

This formula counts tasks that are 7 days late or more and if the percent complete does not equal 100%.  So I just create a new column in my project plans and put this formula at the top.  Brilliant!



  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Sweet. Thanks for sharing! 

  • Tukee
    edited 03/07/18

    Conditional formatting that helps call out similar logic. If Due date is within X days (5 in this example), go orange and if due date has come and gone, go red. Sometimes it's easier to copy a logic then to invent it. The one thing to note is you have to first put in the first condition and then click the tiny arrow to add another condition. Not very intuitive but allows for the dual conditions (%complete and End date) before applying the formatting.


  • What I'm doing is displaying the total number of projects that are at least one week behind on a dashboard.  Can formulas count conditional formatting?

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    No formulas cannot count conditional formatting. Use the formula that customer success gave you. :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!