Calculating Number of Items in a Month based on System Generated date field

Andrea Mayer · 04/17/19

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

TeeM · 04/17/19

Watching with interest

Paul Newcome · 04/18/19

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.

Andrea Mayer · 04/18/19

Thank you, Paul! This worked perfectly. I appreciate the quick and helpful response.

Paul Newcome · 04/19/19

Happy to help!

Calculating Number of Items in a Month based on System Generated date field

Comments

Help Article Resources

Categories