Google-sheets – Count rows that fall between two times

google sheets

Looking to get the count of rows that fall within a certain hour of the day.

I can perform IF statements against the times, but not COUNTIF(S)…

So far I have tried:

=countifs(timevalue(A2:A),">=b2",timevalue(A2:A),"<=c2")

And even tried hard coding the times in, e.g.:

=countifs(timevalue(A2:A),">=05:00:00",timevalue(A2:A),"<=05:59:59")

Also not really sure if/why TIMEVALUE is needed, since it seems I am working with actual datetime cells… however it seemed to help on the IF statements.

Here is my sample spreadsheet showing data: https://docs.google.com/spreadsheets/d/1gEmuc00W1sY_TClx1uR9lqYRRtsHzzWyQvO8o3G8epE/edit#gid=255648219

Best Answer

You want to count of the rows where the time value in Column A falls within the "START" and "END" values in Column B and C

Try =query(A2:C22,"select COUNT(A) where timeofday '"&TEXT(A2,"HH:mm:ss")&"'>= B and C>= timeofday '"&TEXT(A2,"HH:mm:ss")&"'")

  • timeofday is a data type supported by query.
  • Columns B and C are already formatted for time.
  • Column A has a datetime value and needs to be recast as time. TEXT(A2,"HH:mm:ss") converts the value in Column A number into text according to the specified format.
Related Topic