Countifs with Datestamp

[jwbrouse01]

Greetings,

How can I create a countif/ifs formula that will count based on datestamp?

In column A of Sheet1, I have a list of datestamps as below.

9/5/2023 9:59:04 PM

I want to create a countif/ifs formula on another sheet that will count for each day running from 10:00 pm to 10:00 pm, i.e., data on 9/5 @ 10:05 pm actually counts toward 9/6.

If possible, I need to do this without manipulating the data in Sheet1. I want the user to be able to just copy data into Sheet1 and the data is displayed.

Can someone please assist me?

Thank you!

 

[duggie33]

I think this should get you headed in the right direction. If your data is in a table or named range you could make it dynamic.

Book1.xlsx
	A	B	C	D	E	F
1	Datestamps		Date	Start	End	Count
2	9/5/2023 20:59		9/6/2023	9/5/2023 22:00	9/6/2023 22:00	3
3	9/6/2023 11:59		9/7/2023	9/6/2023 22:00	9/7/2023 22:00	3
4	9/7/2023 7:59		9/8/2023	9/7/2023 22:00	9/8/2023 22:00	3
5	9/8/2023 20:59		9/9/2023	9/8/2023 22:00	9/9/2023 22:00	4
6	9/9/2023 19:59		9/10/2023	9/9/2023 22:00	9/10/2023 22:00	2
7	9/10/2023 4:59		9/11/2023	9/10/2023 22:00	9/11/2023 22:00	2
8	9/11/2023 4:59		9/12/2023	9/11/2023 22:00	9/12/2023 22:00	1
9	9/5/2023 19:59		9/13/2023	9/12/2023 22:00	9/13/2023 22:00	1
10	9/6/2023 8:59		9/14/2023	9/13/2023 22:00	9/14/2023 22:00	1
11	9/7/2023 7:59		9/15/2023	9/14/2023 22:00	9/15/2023 22:00	0
12	9/8/2023 18:59		9/16/2023	9/15/2023 22:00	9/16/2023 22:00	0
13	9/9/2023 10:59		9/17/2023	9/16/2023 22:00	9/17/2023 22:00	0
14	9/10/2023 4:59		9/18/2023	9/17/2023 22:00	9/18/2023 22:00	0
15	9/11/2023 18:59		9/19/2023	9/18/2023 22:00	9/19/2023 22:00	0
16	9/12/2023 18:59		9/20/2023	9/19/2023 22:00	9/20/2023 22:00	0
17	9/13/2023 4:59		9/21/2023	9/20/2023 22:00	9/21/2023 22:00	0
18	9/14/2023 21:59		9/22/2023	9/21/2023 22:00	9/22/2023 22:00	1
19	9/22/2023 6:59		9/23/2023	9/22/2023 22:00	9/23/2023 22:00	1
20	9/23/2023 13:59		9/24/2023	9/23/2023 22:00	9/24/2023 22:00	1
21	9/24/2023 7:59		9/25/2023	9/24/2023 22:00	9/25/2023 22:00	1
22	9/25/2023 5:59		9/26/2023	9/25/2023 22:00	9/26/2023 22:00	1
23	9/26/2023 3:59		9/27/2023	9/26/2023 22:00	9/27/2023 22:00	1
24	9/27/2023 14:59
25	9/28/2023 14:59
26	9/6/2023 11:59
27	9/5/2023 21:59
28	9/7/2023 2:59
29	9/8/2023 2:59
30	9/9/2023 2:59
31	9/9/2023 19:59
Sheet3

Cell Formulas
Range	Formula
D2:D23	D2	=C2-2/24
E2:E23	E2	=C2+22/24
F2:F23	F2	=COUNTIFS($A$2:$A$31,">="&D2:D23,$A$2:$A$31,"<"&E2:E23)
Dynamic array formulas.

Hope that helps,

Doug

 

[jwbrouse01]

Wow, that was...really easy.

Thank you for your reply!

 

[duggie33]

Happy to help. Thanks for the feedback.

Doug