Counting Projects that are 1 Week or More Behind Schedule

brandon.elmer

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

Sweet. Thanks for sharing! 

Tukee

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.

brandon.elmer

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

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