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 if date is last 7, 30, X days
I have a sheet that lists feature requests. The system automatically generates a date when the request was submitted. I have summary columns up top of how many features were requested Q1-Q4 but I would like to add in counts for the past 7 days and 30 days. I would like a formula so the counts automatically update without myself having to adjust constantly. I have not been able to figure this out. The formula I used for the quarterly counts were like this:
=COUNTIF([Date Requested]:[Date Requested], AND(@cell >= DATE(2017, 7, 1), @cell < DATE(2017, 10, 1)))
Any help would be greatly appreciated.
Comments
-
Hello,
The formula can be written similarly to the one you've already created, but using the TODAY() function to keep it up to date. This is what I came up with:
Last 7 Days
=COUNTIF([Date Requested]:[Date Requested], AND(@cell <= TODAY(), @cell > TODAY(-7)))
Last 30 Days
=COUNTIF([Date Requested]:[Date Requested], AND(@cell <= TODAY(), @cell > TODAY(-30)))
The TODAY() function would allow this to change every day that you look at this. The negative number in the TODAY() function's parentheses can be set to any amount of days.
-
Hi Leah,
You might consider using the TODAY() function in your formula, for example:
=COUNTIF([Date Requested]:[Date Requested], <TODAY(-30))
Instead of using COUNTIF(AND()), you can use COUNTIFS:
=COUNTIFS([Date Requested]:[Date Requested], <TODAY(-30), [Date Requested]:[Date Requested], >TODAY(-7))
More on these functions in our functions list: https://help.smartsheet.com/functions
-
I had the same question. Thanks.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 33 Webinars
- 7.3K Forum Archives