COUNTIFS Date Formula and Formatting

Michelle Fayed

I will try to explain this the best I can....

I am currently using data shuttle daily to populate a sheet. The imported data does not separate the date and time, so I am using formulas to separate them on my target sheet into separate columns. On a separate data sheet I am trying use a COUNTIFS formula based on a unique identifier and the month to give me the monthly occurrence count. I've tried two formulas:

=COUNTIFS({Station}, Name@row, {Date}, IFERROR(MONTH(@cell), 0) >= 2, {Date}, IFERROR(MONTH(@cell), 0) < 3)

and

=COUNTIFS({Station}, Name@row, {Date}, YEAR(@cell) = 2024, {Date}, MONTH(@cell), 0) >= 2, {Date}, MONTH(@cell), 0) < 3)

Both are returning 0, I am assuming because the date column on my target sheet is listed as Month.Day.Year. So I created a helper column that takes the data using the following formula:

=Date@row

and used the date format tool to yield the data as Year.Month.Day. However, every time the page refreshes the helper column also returns to Month.Day.Year, despite the applied formatting.

I am at a loss. Any help, guidance, or advice is most welcome and appreciate. Thank you in advance!

brianschmidt

I've had trouble with using "MONTH(@cell)" in a COUNTIFS formula. The only way I've been able to get a formula like this to work is to create a helper column to extract the month from the date cell. The column formula is like so:

=IFERROR(MONTH(DATE@row), "")

I would then reference this column as a range instead in your countifs formula.

Hope this helps!:)