1

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.

1
  • 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.
    – mlodhi
    Aug 23, 2023 at 19:09

2 Answers 2

0

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")))
2
  • 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. Aug 23, 2023 at 20:16
  • 1
    I'm glad it was useful! You can mark the question as answered ;)
    – Martín
    Aug 23, 2023 at 21:13
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.