Finding values between a time range

[MAP77]

Hi guys,

I need to find a couple of different things with this set of data.
1) Create a new column called 'day' or 'night' where excel finds all the values between the time 05:30 and 18:00 and calls them 'day' and all the values outside that time block 'night'.
2) Find the average of the values in column B for each day between the hours of 05:30 and 18:00. EG. 1/10/22 would be an average of 21.3489, 2/10/22 would be an average of 20.8088 etc.

I'm not sure if I should do (1) in power query. If so, what would be the formula?

Book1
	A	B
1	Timestamp	Total Avg of Power (kW)
2	1/10/2022 0:00	50.31894
3	1/10/2022 0:30	48.73556
4	1/10/2022 1:00	41.71369
5	1/10/2022 1:30	40.04663
6	1/10/2022 2:00	46.63919
7	1/10/2022 2:30	47.45826
8	1/10/2022 3:00	51.39905
9	1/10/2022 3:30	47.71795
10	1/10/2022 4:00	24.10075
11	1/10/2022 4:30	22.72165
12	1/10/2022 5:00	25.50718
13	1/10/2022 5:30	19.47058
14	1/10/2022 6:00	23.97777
15	1/10/2022 6:30	19.45572
16	1/10/2022 7:00	23.74369
17	1/10/2022 7:30	18.40715
18	1/10/2022 8:00	23.35959
19	1/10/2022 8:30	18.96019
20	1/10/2022 9:00	23.91301
21	1/10/2022 9:30	17.59056
22	1/10/2022 10:00	23.3182
23	1/10/2022 10:30	19.21594
24	1/10/2022 11:00	24.295
25	1/10/2022 11:30	18.74359
26	1/10/2022 12:00	24.78997
27	1/10/2022 12:30	18.7614
28	1/10/2022 13:00	23.62567
29	1/10/2022 13:30	19.25247
30	1/10/2022 14:00	24.00009
31	1/10/2022 14:30	17.35174
32	1/10/2022 15:00	23.45803
33	1/10/2022 15:30	19.49955
34	1/10/2022 16:00	23.8039
35	1/10/2022 16:30	18.4821
36	1/10/2022 17:00	24.41319
37	1/10/2022 17:30	18.03606
38	1/10/2022 18:00	25.14828
39	1/10/2022 18:30	20.79991
40	1/10/2022 19:00	25.17342
41	1/10/2022 19:30	20.28958
42	1/10/2022 20:00	25.30734
43	1/10/2022 20:30	21.1434
44	1/10/2022 21:00	26.2569
45	1/10/2022 21:30	20.76921
46	1/10/2022 22:00	25.2568
47	1/10/2022 22:30	21.36637
48	1/10/2022 23:00	26.23097
49	1/10/2022 23:30	21.00897
50	2/10/2022 0:00	24.64072
51	2/10/2022 0:30	21.53037
52	2/10/2022 1:00	25.90326
53	2/10/2022 1:30	20.68053
54	2/10/2022 2:00	26.29263
55	2/10/2022 2:30	19.58213
56	2/10/2022 3:00	24.48504
57	2/10/2022 3:30	21.60895
58	2/10/2022 4:00	25.0754
59	2/10/2022 4:30	20.43373
60	2/10/2022 5:00	25.82612
61	2/10/2022 5:30	18.91279
62	2/10/2022 6:00	23.04572
63	2/10/2022 6:30	19.37435
64	2/10/2022 7:00	23.38685
65	2/10/2022 7:30	18.81967
66	2/10/2022 8:00	22.26641
67	2/10/2022 8:30	18.19895
68	2/10/2022 9:00	22.79994
69	2/10/2022 9:30	17.75849
70	2/10/2022 10:00	22.61499
71	2/10/2022 10:30	17.72912
72	2/10/2022 11:00	22.47978
73	2/10/2022 11:30	18.40815
74	2/10/2022 12:00	24.00994
75	2/10/2022 12:30	18.24447
76	2/10/2022 13:00	21.60704
77	2/10/2022 13:30	18.90856
78	2/10/2022 14:00	23.22153
79	2/10/2022 14:30	19.15069
80	2/10/2022 15:00	23.48298
81	2/10/2022 15:30	17.904
82	2/10/2022 16:00	22.29922
83	2/10/2022 16:30	19.27028
84	2/10/2022 17:00	23.40998
85	2/10/2022 17:30	18.1261
86	2/10/2022 18:00	25.60093
87	2/10/2022 18:30	20.01902
88	2/10/2022 19:00	24.392
89	2/10/2022 19:30	21.62492
90	2/10/2022 20:00	26.04565
91	2/10/2022 20:30	19.89793
92	2/10/2022 21:00	24.47673
93	2/10/2022 21:30	20.99338
94	2/10/2022 22:00	25.83778
95	2/10/2022 22:30	20.79955
96	2/10/2022 23:00	25.49927
97	2/10/2022 23:30	20.53727
98	3/10/2022 0:00	25.77991
99	3/10/2022 0:30	21.96724
100	3/10/2022 1:00	23.8442
101	3/10/2022 1:30	21.14151
102	3/10/2022 2:00	26.6441
103	3/10/2022 2:30	20.25448
104	3/10/2022 3:00	24.75333
105	3/10/2022 3:30	22.45333
106	3/10/2022 4:00	26.05707
107	3/10/2022 4:30	22.58693
108	3/10/2022 5:00	26.01925
109	3/10/2022 5:30	19.5637
110	3/10/2022 6:00	23.35992
111	3/10/2022 6:30	18.67553
112	3/10/2022 7:00	22.05696
113	3/10/2022 7:30	17.99264
114	3/10/2022 8:00	22.40223
115	3/10/2022 8:30	18.11939
116	3/10/2022 9:00	23.09753
117	3/10/2022 9:30	18.88339
118	3/10/2022 10:00	21.72426
119	3/10/2022 10:30	18.70486
120	3/10/2022 11:00	22.47323
121	3/10/2022 11:30	19.52072
122	3/10/2022 12:00	23.60704
123	3/10/2022 12:30	17.16556
124	3/10/2022 13:00	22.99702
125	3/10/2022 13:30	19.45797
126	3/10/2022 14:00	22.16807
127	3/10/2022 14:30	18.87158
128	3/10/2022 15:00	22.1818
129	3/10/2022 15:30	19.52419
130	3/10/2022 16:00	23.72906
131	3/10/2022 16:30	18.64053
132	3/10/2022 17:00	23.74332
133	3/10/2022 17:30	20.6988
134	3/10/2022 18:00	26.47136
135	3/10/2022 18:30	22.42386
136	3/10/2022 19:00	27.74197
137	3/10/2022 19:30	22.35145
138	3/10/2022 20:00	26.34637
139	3/10/2022 20:30	23.61971
140	3/10/2022 21:00	27.09262
141	3/10/2022 21:30	23.44937
142	3/10/2022 22:00	27.15357
143	3/10/2022 22:30	22.58038
144	3/10/2022 23:00	27.13214
145	3/10/2022 23:30	23.3851
146	4/10/2022 0:00	25.47184
147	4/10/2022 0:30	22.91429
148	4/10/2022 1:00	26.56001
149	4/10/2022 1:30	24.3497
150	4/10/2022 2:00	25.49595
151	4/10/2022 2:30	22.68093
152	4/10/2022 3:00	30.14696
153	4/10/2022 3:30	39.1493
154	4/10/2022 4:00	49.31453
155	4/10/2022 4:30	44.61409
156	4/10/2022 5:00	89.67751
157	4/10/2022 5:30	109.8451
158	4/10/2022 6:00	113.8321
159	4/10/2022 6:30	114.4188
160	4/10/2022 7:00	119.5391
161	4/10/2022 7:30	119.8405
162	4/10/2022 8:00	104.8957
163	4/10/2022 8:30	110.8639
164	4/10/2022 9:00	123.3824
165	4/10/2022 9:30	130.0801
166	4/10/2022 10:00	127.059
167	4/10/2022 10:30	119.2714
168	4/10/2022 11:00	103.917
169	4/10/2022 11:30	118.1359
170	4/10/2022 12:00	136.4574
171	4/10/2022 12:30	119.0629
172	4/10/2022 13:00	105.0671
173	4/10/2022 13:30	93.29017
174	4/10/2022 14:00	117.4678
175	4/10/2022 14:30	130.3958
176	4/10/2022 15:00	130.6096
177	4/10/2022 15:30	125.786
178	4/10/2022 16:00	137.2079
179	4/10/2022 16:30	121.6497
180	4/10/2022 17:00	103.8545
181	4/10/2022 17:30	127.4414
182	4/10/2022 18:00	129.7855
183	4/10/2022 18:30	121.2626
184	4/10/2022 19:00	138.8651
185	4/10/2022 19:30	105.6053
186	4/10/2022 20:00	125.8709
187	4/10/2022 20:30	147.8033
188	4/10/2022 21:00	134.5637
189	4/10/2022 21:30	125.9159
190	4/10/2022 22:00	78.30851
191	4/10/2022 22:30	64.61543
192	4/10/2022 23:00	59.84072
193	4/10/2022 23:30	40.92291
Sheet1

 

[DRSteele]

I think this will do it. I only included a few rows.

You would be better off making the data into an Excel Table. This task could be done then with a Pivot Table, and PowerQuery would require the data to be in an Excel Table anyway.

MrExcel posts19.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Timestamp	Total Avg of Power (kW)	Date	Time	Period		Date	Day		5:30:00 AM	6:00:01 PM
2	10/1/22 12:00 AM	50.3189411	10/1/2022	12:00:00 AM	Night		10/1/2022	21.3489794
3	10/1/22 12:30 AM	48.73555833	10/1/2022	12:30:00 AM	Night		10/2/2022	20.8088817
4	10/1/22 1:00 AM	41.71368612	10/1/2022	1:00:00 AM	Night		10/3/2022	20.9934881
5	10/1/22 1:30 AM	40.04662945	10/1/2022	1:30:00 AM	Night		10/4/2022	118.967566
6	10/1/22 2:00 AM	46.63919053	10/1/2022	2:00:00 AM	Night
7	10/1/22 2:30 AM	47.45825667	10/1/2022	2:30:00 AM	Night
8	10/1/22 3:00 AM	51.39904722	10/1/2022	3:00:00 AM	Night
9	10/1/22 3:30 AM	47.7179456	10/1/2022	3:30:00 AM	Night
10	10/1/22 4:00 AM	24.10075168	10/1/2022	4:00:00 AM	Night
11	10/1/22 4:30 AM	22.72164502	10/1/2022	4:30:00 AM	Night
12	10/1/22 5:00 AM	25.5071811	10/1/2022	5:00:00 AM	Night
13	10/1/22 5:30 AM	19.47058113	10/1/2022	5:30:00 AM	Day
14	10/1/22 6:00 AM	23.97776718	10/1/2022	6:00:00 AM	Day
Sheet38

Cell Formulas
Range	Formula
C2:C14	C2	=TRUNC(A2)
D2:D14	D2	=A2-C2
E2:E14	E2	=IF((D2>=$J$1)*(D2<=$K$1),"Day","Night")
G2:G5	G2	=UNIQUE(C2:C193)
H2:H5	H2	=AVERAGEIFS($B$2:$B$193,$C$2:$C$193,G2,$E$2:$E$193,$H$1)
Dynamic array formulas.

 

[MAP77]

Thank you so much @DRSteele. This works perfectly.

 

[DRSteele]

Super! You're welcome.

Do notice the 6:00:01 pm thing. Excel is weird sometimes, and when the Excel DateTime Serial number is turned into a date and subtracted from it to get the time, it reports a value not exactly the same. Something to do with Excel precision of 14 digits. Whatever.