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!
Comments

Sweet. Thanks for sharing!

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?

No formulas cannot count conditional formatting. Use the formula that customer success gave you.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 406 Global Discussions
 218 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!