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!

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Calculate number of days between two dates in the same column on the same sheet
Hello, Can a combination of the INDEX(COLLECT and NETDAYS Formula be used to get the number of days between two dates in the same column? I want to get the number of days between two dates on the same sheet, in the same column, that have matching names and different project types. For Example: Studio Name is equal to…
Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…