Having problems with Date Ranges
I want to count install for each month.
=COUNTIFS({Install Date5}, "Install Date", {Install Date9}, AND(@cell >= DATE(5,01,2024). @cell <= DATE(5,31,2024)))
Answers
-
I preface this by recommending you NOT do a summary at the top of the sheet (because that'll prevent you from using column formulas, and will be problematic in terms of scaling once this sheet rolls over to June and July and so on). Instead, add a column with a column formula that converts the Install Date to a Year/Month… for example, 2024-05 :
=YEAR([Install Date]@row)+IF(MONTH([Install Date]@row)<10,"-0","-")+MONTH([Install Date]@row)
(The "IF" part in the middle of the formula adds a leading 0 to one-digit rows.)
You can then create a Report, grouping by that new column and summarizing with COUNT.
I find that with dates, COLLECT is a little more forgiving than COUNTIF/S. So with that said, the formula I would use for the UNPARSEABLE cell on line 2 is:=COUNT(COLLECT([Quote Number]:[Quote Number], [Install Date]:[Install Date], >=Status@row, [Install Date]:[Install Date], <=[Internal Owner]@row))
If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!
-
@Kerry, St. Thomas, I really appreciate your prompt response. The first formula does not fit the case I was looking for. The second one more what I would need. Do I need to convert the date to Year-Month-Day for it to work?
BTW - Your profile says St. Thomas, is that the Virgin Island or Jamaica?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!