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!