Count if date is in the past
Hi All,
I am looking for a solution to a problem, I am trying to pull together a chart for a dashboard, displaying the number of overdue documents under review we have - the sheet is set up as below, and I have set up a 'metric sheet' however I am not sure this is the best solution. I have been using this =COUNTIFS({Master Document Tracker [DO NOT DELETE] Range 3}, <=TODAY()). But i have quickly realised, if there is an over due date in this column there will potentially be one in each column for this row. how do I count if any cells within a row have overdue dates but eliminate duplicate counts within the same row.
all suggestions welcome! thank you in advance!
Best Answer
-
Your syntax is a bit off. Try this:
=IF(COUNTIFS([Expected Draft Submit Date]@row:[Expected Doc Control Master complete]@row, @cell >= TODAY())> 0, "Overdue", "On Schedule")
Answers
-
@Kirsteen Leckie You could use a helper column on this sheet that looks to see if there is any overdue dates, then count the helper column in your metric sheet. Formula could be something like this but will need to be modified:
=if(countifs([expecteddraftdate]@row:[expected submission]@row) >=today(), "Overdue", "On Schedule")
Then on your metric sheet count the number of "Overdue".
Does that concept make sense?
-
@Samuel Mueller i like this idea - however the suggested has come back as #UNPARSEABLE.
i changed it to include the columns not shown in the picture - =IF(COUNTIFS([Expected Draft Submit Date]@row:[Expected Doc Control Master complete]@row >= TODAY(), "Overdue", "On Schedule")) but now coming back as #INCORRECT ARGUMENT SET
any ideas what I have missed?
-
Your syntax is a bit off. Try this:
=IF(COUNTIFS([Expected Draft Submit Date]@row:[Expected Doc Control Master complete]@row, @cell >= TODAY())> 0, "Overdue", "On Schedule")
-
@Paul Newcome and @Samuel Mueller thank you both! exactly what I was looking for!!
-
@Paul Newcome Thanks Paul for the fix
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!