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
- 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!