Warning -MONTH does not work as expected.

WldAcrUsa

All,

All our date ranges were setup using the 31st, which turns out to have been an issue. Even though the month is specified, the number of days (in this case 31) overrides the month entry and returned 31 days of results instead of just February.

=COUNTIFS({ICR Tracker Review Range}, >=DATE(2023, 2, 1), {ICR Tracker Review Range}, <=DATE(2023, 2, 31))

-Result was 173 which was high. When I changed the formula to only count to the 28th, I received the correct result 154.

=COUNTIFS({ICR Tracker Review Range}, >=DATE(2023, 2, 1), {ICR Tracker Review Range}, <=DATE(2023, 2, 28))

Genevieve P.

Hi @WldAcrUsa

You are correct - in the DATE Function, if you specify a Day in that month that doesn't exist, the date will roll over to the next month.

So since Feb 31st, 2023 doesn't exist, the Date reads that as Feb 28th + 3 days, or March 3rd:

If you're looking to specifically search for a Month, I would recommend using the MONTH Function instead:

=COUNTIFS({ICR Tracker Review Range}, IFERROR(MONTH(@cell), "") = 2)

Note that I've wrapped this in an IFERROR to eliminate any blank cells or cells with text in them.

Cheers,

Genevieve

WldAcrUsa

@Genevieve,

Hi, that more than doubles my counts. My guess a year would then need to be specified.

Genevieve P.

Hi @WldAcrUsa

Ah yes! If you have multiple years in the same sheet then you're right, we can specify a year as well:

=COUNTIFS({ICR Tracker Review Range}, IFERROR(MONTH(@cell), "") = 2, {ICR Tracker Review Range}, IFERROR(Year(@cell), "") = 2023)

Another option would be to reference a date cell in the sheet, so instead of adjusting a formula you can simply select a new start and end date for your calculations:

=COUNTIFS({ICR Tracker Review Range}, >= [Start Date]@row, {ICR Tracker Review Range}, <= [End Date]@row)

Or you could use the TODAY function if you want to search specifically through the current month:

=COUNTIFS({ICR Tracker Review Range}, IFERROR(MONTH(@cell), "") = MONTH(TODAY()), {ICR Tracker Review Range}, IFERROR(Year(@cell), "") = YEAR(TODAY()))

I hope that helps! 🙂

Genevieve