Welcome!

Warning -MONTH does not work as expected.

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))

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Finding Unique Dates
I looking to find unique values, in order, and separate them into columns. The "Client Update Date" column will contain dates, not in order and some dates may be duplicated. In the "1st Date Feedback I'm getting the correct date of 12/12/25 with the formula: =MIN([Client Update Date]:[Client Update Date]) In the "2nd Date…
SUMIFS keeps returning 0
I'm trying to display a sum of the column Total Funds Available when Status = Apps Open, and Value Score = 5. Here is the formula I have: =SUMIFS([Total Funds Available]:[Total Funds Available], Status:Status, ="Apps Open", [Value Score]:[Value Score], ="5") There are rows that meet these conditions and should display a…
Create Sequential, Unique ID for certain rows as column formula
I need a column formula that can add sequential, unique IDs to data like this coming in from a form: Task Type Task ID Manual M001 Automated Manual M002 Manual M003 Manual M004 Automated Manual M005 Task IDs that aren’t the manual type need sequential numbering within their type (i.e. A001, A002, etc). Here’s the kicker:…