Date range formula
Hello,
How would i write a formula when looking for data between a certain period i.e. I want to find the number of tasks marked 'complete' between March 1 through March 31? Thanks.
Answers
-
Hello,
You will want to make a helper column called month that has the following formula:
=MONTH(Date@row)
Then you can use this formula in your count column:
=COUNTIFS([Month]:[Month], =3, [Task]:[Task], ="complete")
That should give you the count you want.
If you would like a more robust formula, you could do this:
=IF(DAY(Date@row) =1, =COUNTIFS([Month]:[Month], =Month@row, [Task]:[Task], ="complete"),"")
This formula only works if you have a beginning day of 1 for each month in your data. It checks if the date is the first day, then if it is, it will count all tasks that are complete and within that month.
Hope this helps.
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
- 141 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!