How to sum a column between 2 dates and times

[ascott523]

I have a report that I need to sum all values between 9 am on day 1 and 5 am on day 2. Unfortunately my report format is the below. This report updates every day so I need a dynamic formula that will always sum 9 am to 5 am based on a date stated in a different cell. So for example, if my reference cell says 10/4/22, I need the sum of column 2 for 10/4/22 9:00:00 AM through 10/5/2022 5:00:00 AM. I've tried just reformatting the first cell into a date but the time format won't let me do a between formula because the dates are different and the time isn't in a 24 hour format and I'm not sure how to get it to that point. Formulas or macro solutions would be welcome, thanks in advance!

10/5/2022 5:00:00 AM	61​
10/5/2022 4:00:00 AM	40​
10/5/2022 3:00:00 AM	25​
10/5/2022 2:00:00 AM	15​
10/5/2022 1:00:00 AM	69​
10/5/2022 12:00:00 AM	107​
10/4/2022 11:00:00 PM	107.84​
10/4/2022 10:00:00 PM	110.95​
10/4/2022 9:00:00 PM	104​
10/4/2022 8:00:00 PM	108​
10/4/2022 7:00:00 PM	115​
10/4/2022 6:00:00 PM	113​
10/4/2022 5:00:00 PM	116​
10/4/2022 4:00:00 PM	114​
10/4/2022 3:00:00 PM	120​
10/4/2022 2:00:00 PM	123​
10/4/2022 1:00:00 PM	120​
10/4/2022 12:00:00 PM	117​
10/4/2022 11:00:00 AM	116​
10/4/2022 10:00:00 AM	119​
10/4/2022 9:00:00 AM	121​
10/4/2022 8:00:00 AM	123​
10/4/2022 7:00:00 AM	117​
10/4/2022 6:00:00 AM	104​
10/4/2022 5:00:00 AM	67​

 

[JamesCanale]

MrExcelPlayground12.xlsx
	A	B	C	D	E
2	Time	Thing
3	10/5/2022 5:00	61		10/4/2022	2041.79
4	10/5/2022 4:00	40
5	10/5/2022 3:00	25
6	10/5/2022 2:00	15
7	10/5/2022 1:00	69
8	10/5/2022 0:00	107
9	10/4/2022 23:00	107.84
10	10/4/2022 22:00	110.95
11	10/4/2022 21:00	104
12	10/4/2022 20:00	108
13	10/4/2022 19:00	115
14	10/4/2022 18:00	113
15	10/4/2022 17:00	116
16	10/4/2022 16:00	114
17	10/4/2022 15:00	120
18	10/4/2022 14:00	123
19	10/4/2022 13:00	120
20	10/4/2022 12:00	117
21	10/4/2022 11:00	116
22	10/4/2022 10:00	119
23	10/4/2022 9:00	121
24	10/4/2022 8:00	123
25	10/4/2022 7:00	117
26	10/4/2022 6:00	104
27	10/4/2022 5:00	67
28	10/4/2022 4:00	125
29	10/4/2022 3:00	124
30	10/4/2022 2:00	148
31	10/4/2022 1:00	111
32	10/4/2022 0:00	114
33	10/3/2022 23:00	17
34	10/3/2022 22:00	63
35	10/3/2022 21:00	5
36	10/3/2022 20:00	105
37	10/3/2022 19:00	115
38	10/3/2022 18:00	18
39	10/3/2022 17:00	130
40	10/3/2022 16:00	145
41	10/3/2022 15:00	62
42	10/3/2022 14:00	121
43	10/3/2022 13:00	80
44	10/3/2022 12:00	72
45	10/3/2022 11:00	138
46	10/3/2022 10:00	43
47	10/3/2022 9:00	14
48	10/3/2022 8:00	63
49	10/3/2022 7:00	93
50	10/3/2022 6:00	38
51	10/3/2022 5:00	52
52	10/3/2022 4:00	65
53	10/3/2022 3:00	79
54	10/3/2022 2:00	150
55	10/3/2022 1:00	126
56	10/3/2022 0:00	99
57	10/2/2022 23:00	87
58	10/2/2022 22:00	74
59	10/2/2022 21:00	114
60	10/2/2022 20:00	120
61	10/2/2022 19:00	104
62	10/2/2022 18:00	42
63	10/2/2022 17:00	21
64	10/2/2022 16:00	101
65	10/2/2022 15:00	47
66	10/2/2022 14:00	66
67	10/2/2022 13:00	85
68	10/2/2022 12:00	43
69	10/2/2022 11:00	14
70	10/2/2022 10:00	63
71	10/2/2022 9:00	93
Sheet21

Cell Formulas
Range	Formula
E3	E3	=SUMIFS(Table1[Thing],Table1[Time],">="&(INT(D3)+9/24),Table1[Time],"<="&(INT(D3)+29/24))