Count entry by time

[admvlad]

I have time date in a column on an excel sheet in this format:

17:39:00

17:18:00

16:49:00

14:54:00

14:11:00

12:15:00

12:07:00

23:00:00

17:09:00

17:01:00

8:54:00

8:21:00

6:07:00

0:06:00

19:23:00

13:19:00

12:55:00

4:05:00

23:10:00

20:39:00

20:17:00​

I need to know how many instances occur by time block. The blocks are 0000-0400, 0400-1200, 1200-2000 and 2000-0000. Can a MOD formula be used?

 

[hagia_sofia]

Hello, if there is only time in the column would this not work?

=COUNTIFS($A$2:$A$22,">="&TIME(0,0,0),$A$2:$A$22,"<="&TIME(3,59,59))

and so on for other intevals (should not the intervals be disjunct?)

 

[xlhelp15]

How about this?

0000-0400	=COUNTIFS(D:D,">=00:00:00",D:D,"<=04:00:00")
0400-1200	=COUNTIFS(D:D,">=04:00:01",D:D,"<=12:00:00")
1200-2000	=COUNTIFS(D:D,">=12:00:01",D:D,"<=20:00:00")
2000-0000	=COUNTIFS(D:D,">=20:00:01",D:D,"<=24:00:00")

 

[admvlad]

Thank you! That works perfectly