# different results for COUNTIFS on date range when using > vs. >=

I haven't quite been able to wrap my head around this one, but I've got a spreadsheet that is a simple list of dates, formatted thusly:

``````11/1/2022 0:12:54

11/1/2022 6:45:43

11/1/2022 7:13:15
``````

... and so on

This covers from 11/1 to 11/30 and has 538 rows.

This formula gives me a count of 538 as expected:

``````=(COUNTIFS(A1:A, ">"&DateValue("10-31-2022"), A1:A, "<"&DateValue("12-1-2022")))
``````

That is to say "dates greater than October 31 2022 and Less than December 1 2022" which represents the entirety of this dataset.

The following formula gives me 506 and I can't figure out why, because these should be functionally equivalent:

``````=(COUNTIFS(A1:A, ">="&DateValue("11-1-2022"), A1:A, "<="&DateValue("11-30-2022")))
``````

Greater than or equal to November 1, and less than or equal to November 30.

That's the same thing isn't it?

"A" is formatted as Number->Date/Time.

I should note that this gives 538:

``````=(COUNTIFS(A1:A, ">="&DateValue("11-1-2022"), A1:A, "<="&DateValue("12-1-2022")))
``````

There are 32 records with dates of 11/30/2022.

• According to my understanding, `<` comparisons are exclusive bounds and `=` & `<=` are inclusive bounds. A solution would be to be aware of how date comparisons work internally in Excel and use inclusive or exclusive bounds intentionally to get the desired result. Commented Aug 23, 2023 at 19:09

Dates are considering as numbers inside Google Sheets. Days are whole numbers, and the hours, minutes and seconds are fractions of them (hence, they're decimals). For example, today's date is 45161 (it's calculated from 31/12/1899, you can read this article for example), but today at midday is 45161,5.

In your second formula you're excluding anything after 00:00. So, to be able to consider everything up to the last hour of the last date you could use this:

``````=(COUNTIFS(A1:A, ">="&DateValue("11-1-2022"), A1:A, "<"&DateValue("12-1-2022")))
``````
• Thanks. I don't have enough reputation to upvote this but it turns out that you are essentially right on the money. It was being considered that everything after 00:00 was slightly higher than what "DateValue" returned because it was comparing the whole number of the day, and the actual value was slightly higher (by some decimal fraction) than that whole number. Commented Aug 23, 2023 at 20:16
• I'm glad it was useful! You can mark the question as answered ;) Commented Aug 23, 2023 at 21:13

To compare dates to dates, apply `datevalue()` to the date times in column `A1:A`. The function will strip the time part, leaving the date. Note that you should apply it to `criteria_range` rather than the lower and upper bounds you are comparing the data to.

To avoid locale issues such as differences in date formats like `m/d/yyyy` and `d/m/yyyy`, use the ISO8601 standard date format `yyyy-MM-dd`. It works in any locale.

To apply `datevalue()` to a whole column in one go, wrap the formula in `arrayformula()`, like this:

``````=arrayformula(countifs(
datevalue(A1:A), ">=" & value("2022-11-01"),
datevalue(A1:A), "<=" & value("2022-11-30")
))
``````

If you put the lower bound in cell `D1` and upper bound in cell `D2`, you can use this:

``````=arrayformula(countifs(
datevalue(A1:A), ">=" & D1,
datevalue(A1:A), "<=" & D2
))
``````

All this calculation happens using dateserial values rather than formatted date objects. See Working with date and time values in Google Sheets, arrayformula(), datevalue() and value().