Calculating Number of Items in a Month based on System Generated date field
Hello all -
Needing help with a formula to calculate how many requests are submitted via a webform for each month.
2 questions related to this:
1. Can a formula pull just the month from a System Generated cell: Created by (Date)?
2. Where are errors in this formula:
=COUNTIF({Date Submitted}, MONTH(@cell) = 1)
'Date Submitted' is the column name and I have tried it as a regular Date column and System Generated.
Thank you!
Andrea
Comments
-
Watching with interest
-
1. Yes it can.
2. There are no issues with the formula itself, but any blank cells within a range (including blank rows at the bottom of the sheet if the entire column is selected) will through an error for the MONTH function.
To avoid this we can wrap the MONTH function in an IFERROR. This will allow us to designate a number that will essentially "replace" the missing month when a cell within the range does not have a date in it.
Give this one a whirl...
=COUNTIF({Date Submitted}, IFERROR(MONTH(@cell), 0) = 1)
This replaces the missing month with the number zero. Because there are no month numbers of zero, this will not throw any false positives. In all reality, you can use any number that is not a month number, so 0 and then 13 through infinity. Any numbers 1 through twelve would need to have a decimal. 0 is just the easiest one to use because you can also use this in a YEAR, WEEKNUMBER, DAY, etc... Any function that requires a number really.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you, Paul! This worked perfectly. I appreciate the quick and helpful response.
-
Happy to help!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 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
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!