Count by time block in Date/Time Group column

[admvlad]

I have a column with a date/time. I need to know how many instances occur by time block. The blocks are 0000-0400, 0400-1200, 1200-2000 and 2000-0000. I have tried =COUNTIFS(D:D,">=00:00:00",D:D,"<=04:00:00") but it returns nothing. That does work in another sheet that only has time in the column. The cells show this format:

19/2148

20/1439

22/1214

But the actual data is in this format: 4/19/2024 21:48:00

 

[JamesCanale]

MrExcelPlayground23.xlsx
	A	B	C	D
1	9/12/2024 9:05		0000-4000	4
2	9/12/2024 22:17		4000-1200	10
3	9/12/2024 22:22		1200-2000	4
4	9/13/2024 1:10		2000-0000	8
5	9/13/2024 4:28
6	9/13/2024 12:16
7	9/14/2024 3:37
8	9/14/2024 21:17
9	9/15/2024 5:27
10	9/15/2024 9:57
11	9/16/2024 7:10
12	9/17/2024 6:07
13	9/17/2024 12:57
14	9/18/2024 11:43
15	9/18/2024 19:46
16	9/18/2024 22:26
17	9/19/2024 13:27
18	9/20/2024 2:13
19	9/20/2024 22:11
20	9/21/2024 2:00
21	9/21/2024 22:40
22	9/22/2024 10:25
23	9/23/2024 5:56
24	9/23/2024 20:23
25	9/24/2024 4:01
26	9/24/2024 20:32
Sheet20

Cell Formulas
Range	Formula
D1	D1	=ROWS(FILTER(A1:A26,MOD(A1:A26,1)<4/24))
D2	D2	=ROWS(FILTER(A1:A26,(MOD(A1:A26,1)>=4/24)*(MOD(A1:A26,1)<0.5)))
D3	D3	=ROWS(FILTER(A1:A26,(MOD(A1:A26,1)>=0.5)*(MOD(A1:A26,1)<20/24)))
D4	D4	=ROWS(FILTER(A1:A26,MOD(A1:A26,1)>=20/24))

 

[pitchoute]

1) Count instances between 00:00 and 04:00:

=SUMPRODUCT(--(MOD(D2:D1000,1)>=TIMEVALUE("00:00:00")), --(MOD(D2:D1000,1)<TIMEVALUE("04:00:00")))

2) Count instances between 04:00 and 12:00:

=SUMPRODUCT(--(MOD(D2:D1000,1)>=TIMEVALUE("04:00:00")), --(MOD(D2:D1000,1)<TIMEVALUE("12:00:00")))

3) Count instances between 12:00 and 20:00:

=SUMPRODUCT(--(MOD(D2:D1000,1)>=TIMEVALUE("12:00:00")), --(MOD(D2:D1000,1)<TIMEVALUE("20:00:00")))

4) Count instances between 20:00 and 00:00 (crossing midnight):

=SUMPRODUCT(--( (MOD(D2:D1000,1)>=TIMEVALUE("20:00:00")) + (MOD(D2:D1000,1)<TIMEVALUE("00:00:00")) >0))

Note: Adjust D2:D1000 to match the range of your data.

 

[admvlad]

Thank you!

That works great for the most part. The only issue I am having is with the D1 formula, it seems to be counting all blank cells as well. Is there a way to get it to ignore a cell if it has nothing in it? The other 3 are completely accurate.

 

[JamesCanale]

=ROWS(FILTER(A1:A27,(MOD(A1:A27,1)<4/24)*(A1:A27<>"")))

 

[admvlad]

=ROWS(FILTER(A1:A27,(MOD(A1:A27,1)<4/24)*(A1:A27<>"")))

Thank you!

You are all amazing. Thanks so much