Last 7 Days Formula
I am trying to calculate how many IT tickets have been moved to the status of "completed" within the last 7 days. The formula returns a value of 6 but when I use the filters on the report, it returns the correct value of 2. Can someone help me with the formula so it returns the correct value of 2?
The formula I have is:
=COUNTIFS(Status:Status, "Complete", [Due Date - OVERALL]:[Due Date - OVERALL], >=TODAY(-7))
Best Answer
-
Try adding in some criteria to be less than today.
=COUNTIFS(Status:Status, "Complete", [Due Date - OVERALL]:[Due Date - OVERALL], AND(@cell >= TODAY(-7), @cell <= TODAY()))
Answers
-
Can you share the values from your sheet, and the column types?
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
The Status is a drop-down single select column. The Due Date - OVERALL column is a date select column. Using the formula I get 6 tickets that have moved to the status of "complete" in the last 7 days however when I filter the status column to be "complete" and the date column to be "in the last (7) days" I return a value of 2.
-
Try adding in some criteria to be less than today.
=COUNTIFS(Status:Status, "Complete", [Due Date - OVERALL]:[Due Date - OVERALL], AND(@cell >= TODAY(-7), @cell <= TODAY()))
-
I'm wondering if there are text values in your date select column. Check the box in the column properties to restrict to dates only, and verify all the dates are actually in there as dates by re-selecting them.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!