Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Count Past Due Tasks
Hi there - I'm new to formatting / formulas in smartsheet (and excel for that matter) and haven't had much luck creating a formula that counts tasks that are past due. While able to use conditional formatting to visually identify these items I'm trying to roll this up into a project overview; the overview/dashboard - currently includes task count by owener, by status, etc.
Am I on the right track using a COUNTIF and TODAY type functions? Perhaps a formula that would assign a count or criteria to any dates that are greater than today and, from there use a COUNTIF to count up the number of those items? My attempts thus far have returend various errors.
I appreciate any assistnce the community can offer!
-C
Comments
-
You are on the right track with COUNTIF and TODAY functions. Try this...
Add a checkbox column to your sheet with a formula that will check the box if the corresponding due date is in the past.
=IF([End Date]1 < TODAY(), 1)
Then build a COUNTIF that counts the number of boxes checked.
=COUNTIF(CB:CB, 1)
CB is the name of the checkbox column. By not including row numbers, it will count the entire column and not just a range.
-
I am trying to do something very similar. I would like to do it without adding additional columns. Can anyone tell me why this formula will not work or what can be done to correct it.
=COUNTIF([Due Date]:[Due Date], - TODAY(),>0)
I get #MISSING OR INVALID PARAMETERS
I have also tried this with same results.
=COUNTIF([Due Date]:[Due Date], <TODAY())
-
It will not work because you can only use < or > in a COUNTIF with numerical values - you cannot use dates.
From the help center article on formulas:
- Example: =COUNTIF(Done:Done, 1)
- Result: 2
- Example: =COUNTIF(Cost:Cost, ">" + 200)
NOTE: This syntax works for numeric values only. - Result: 2
-
Hi Chris, can you show us a sample of the formulas, My skills are less than yours and I am trying to do some similar to what you did. I will really appreciate that.
Thanks
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives