Filter date time data so I only get one data point per hour

[jappi]

Hi - data sample attached. The date time reporting intervals in column A are every 10 mins, all day, across multiple dates. My goal:

1. Create a line graph that only has an event per hour. I don't really mind what time within the hour it is, as long as its consistent. E.g. show every event at h:06m
2. I want to filter out anything that is before 8am and after 6pm and ideally, not include Saturday or Sunday but the latter is a nice to have which I could manage manually if need be
3. I can then select which of the columns from B onwards I want to show in the line graphs

Thanks in advance

 

[Peter_SSs]

@jappi
You did not address the issue raised in post #17

Your minutes in your data change to 5 minutes

In your sample data from post #14 ..

Can you shed any light on what is happening there and explain how the following should be managed in light of that change?

I don't really mind what time within the hour it is, as long as its consistent. E.g. show every event at h:06m

 

[Peter_SSs]

Looking at the thread again it also seems a little strange that you asked for weekends to be excluded yet the only sample data we have consists of nothing but weekend.

I am also unsure if the data is always produced for the 06, 16, 26 etc minutes (ignoring the change to 05 for the moment) or on another occasion the minutes might be
03, 13, 23, etc or
00, 10, 20, etc

If the minutes end with anything other than zero, @awoohaw your formula in post 17 will return times after 6pm
If the minutes could end with zero then @Cubist your formula in post 18 (& awoohaw's modification in post 19) would omit the exact time of 6pm (I know this possibility is not mentioned by the OP so my comment on this may not be relevant.)

@Cubist, the weekday function would make it shorter:

True, but you haven't applied it correctly. You formula excludes Sunday only.

This is my attempt. I have expanded the sample data to include some week days and some weekend days.
Pending the outcome of the change from 06 minutes to 05 minutes, I have tried to report the first available time in each of the relevant hours. So I have both 06 and 05 in my results.
I have only shown two columns from sample data and results otherwise I exceed the XL2BB and/or forum limits.

24 03 16.xlsm
	A	B
1	Time	Temperature
2	Thu 7/3/24 0:06	24.5 ℃
3	Thu 7/3/24 0:16	24.5 ℃
4	Thu 7/3/24 0:26	24.5 ℃
5	Thu 7/3/24 0:36	24.5 ℃
6	Thu 7/3/24 0:46	24.6 ℃
7	Thu 7/3/24 0:56	24.5 ℃
8	Thu 7/3/24 1:06	24.5 ℃
9	Thu 7/3/24 1:16	24.5 ℃
10	Thu 7/3/24 1:26	24.5 ℃
11	Thu 7/3/24 1:36	24.5 ℃
12	Thu 7/3/24 1:46	24.5 ℃
13	Thu 7/3/24 1:56	24.5 ℃
14	Thu 7/3/24 2:06	24.5 ℃
15	Thu 7/3/24 2:16	24.5 ℃
16	Thu 7/3/24 2:26	24.5 ℃
17	Thu 7/3/24 2:36	24.5 ℃
18	Thu 7/3/24 2:46	24.6 ℃
19	Thu 7/3/24 2:56	24.6 ℃
20	Thu 7/3/24 3:06	24.6 ℃
21	Thu 7/3/24 3:16	24.5 ℃
22	Thu 7/3/24 3:26	24.5 ℃
23	Thu 7/3/24 3:36	24.6 ℃
24	Thu 7/3/24 3:46	24.6 ℃
25	Thu 7/3/24 3:56	24.6 ℃
26	Thu 7/3/24 4:06	24.6 ℃
27	Thu 7/3/24 4:16	24.5 ℃
28	Thu 7/3/24 4:26	24.6 ℃
29	Thu 7/3/24 4:36	24.6 ℃
30	Thu 7/3/24 4:46	24.6 ℃
31	Thu 7/3/24 4:56	24.6 ℃
32	Thu 7/3/24 5:06	24.6 ℃
33	Thu 7/3/24 5:16	24.6 ℃
34	Thu 7/3/24 5:26	24.6 ℃
35	Thu 7/3/24 5:36	24.6 ℃
36	Thu 7/3/24 5:46	24.6 ℃
37	Thu 7/3/24 5:56	24.6 ℃
38	Thu 7/3/24 6:06	24.6 ℃
39	Thu 7/3/24 6:16	24.7 ℃
40	Thu 7/3/24 6:26	24.8 ℃
41	Thu 7/3/24 6:36	24.9 ℃
42	Thu 7/3/24 6:46	25 ℃
43	Thu 7/3/24 6:56	25.1 ℃
44	Thu 7/3/24 7:06	25.3 ℃
45	Thu 7/3/24 7:16	25.4 ℃
46	Thu 7/3/24 7:26	25.5 ℃
47	Thu 7/3/24 7:36	25.7 ℃
48	Thu 7/3/24 7:46	25.8 ℃
49	Thu 7/3/24 7:56	25.9 ℃
50	Thu 7/3/24 8:06	26.1 ℃
51	Thu 7/3/24 8:16	26.2 ℃
52	Thu 7/3/24 8:26	26.3 ℃
53	Thu 7/3/24 8:36	26.4 ℃
54	Thu 7/3/24 8:46	26.5 ℃
55	Thu 7/3/24 8:55	26.6 ℃
56	Thu 7/3/24 9:05	26.7 ℃
57	Thu 7/3/24 9:16	26.4 ℃
58	Thu 7/3/24 9:25	25.8 ℃
59	Thu 7/3/24 9:35	25.3 ℃
60	Thu 7/3/24 9:45	25 ℃
61	Thu 7/3/24 9:55	24.7 ℃
62	Thu 7/3/24 10:05	24.4 ℃
63	Thu 7/3/24 10:15	24.2 ℃
64	Thu 7/3/24 10:25	23.9 ℃
65	Thu 7/3/24 10:35	23.7 ℃
66	Thu 7/3/24 10:45	23.5 ℃
67	Thu 7/3/24 10:55	23.3 ℃
68	Thu 7/3/24 11:05	23.2 ℃
69	Thu 7/3/24 11:15	23.1 ℃
70	Thu 7/3/24 11:25	23.1 ℃
71	Thu 7/3/24 11:35	23.2 ℃
72	Thu 7/3/24 11:45	23.3 ℃
73	Thu 7/3/24 11:55	23.4 ℃
74	Thu 7/3/24 12:05	23.3 ℃
75	Thu 7/3/24 12:15	23.3 ℃
76	Thu 7/3/24 12:25	23.3 ℃
77	Thu 7/3/24 12:35	23.1 ℃
78	Thu 7/3/24 12:45	23.2 ℃
79	Thu 7/3/24 12:55	23.2 ℃
80	Thu 7/3/24 13:05	23.1 ℃
81	Thu 7/3/24 13:15	23.1 ℃
82	Thu 7/3/24 13:25	23.1 ℃
83	Thu 7/3/24 13:35	23 ℃
84	Thu 7/3/24 13:45	23.1 ℃
85	Thu 7/3/24 13:55	23.1 ℃
86	Thu 7/3/24 14:05	23.1 ℃
87	Thu 7/3/24 14:15	23.1 ℃
88	Thu 7/3/24 14:25	23 ℃
89	Thu 7/3/24 14:35	23 ℃
90	Thu 7/3/24 14:45	23 ℃
91	Thu 7/3/24 14:55	23 ℃
92	Thu 7/3/24 15:05	23 ℃
93	Thu 7/3/24 15:15	23 ℃
94	Thu 7/3/24 15:25	23 ℃
95	Thu 7/3/24 15:35	23.1 ℃
96	Thu 7/3/24 15:45	23 ℃
97	Thu 7/3/24 15:55	22.8 ℃
98	Thu 7/3/24 16:05	22.8 ℃
99	Thu 7/3/24 16:15	22.7 ℃
100	Thu 7/3/24 16:25	22.7 ℃
101	Thu 7/3/24 16:35	22.7 ℃
102	Thu 7/3/24 16:45	22.9 ℃
103	Thu 7/3/24 16:55	23.3 ℃
104	Thu 7/3/24 17:05	23.7 ℃
105	Thu 7/3/24 17:15	23.9 ℃
106	Thu 7/3/24 17:25	24 ℃
107	Thu 7/3/24 17:35	24.2 ℃
108	Thu 7/3/24 17:45	24.4 ℃
109	Thu 7/3/24 17:55	24.5 ℃
110	Thu 7/3/24 18:05	24.6 ℃
111	Thu 7/3/24 18:15	24.6 ℃
112	Thu 7/3/24 18:25	24.7 ℃
113	Thu 7/3/24 18:35	24.7 ℃
114	Thu 7/3/24 18:45	24.8 ℃
115	Thu 7/3/24 18:55	24.8 ℃
116	Thu 7/3/24 19:05	24.9 ℃
117	Thu 7/3/24 19:15	24.9 ℃
118	Thu 7/3/24 19:25	24.9 ℃
119	Thu 7/3/24 19:35	24.9 ℃
120	Thu 7/3/24 19:45	25 ℃
121	Thu 7/3/24 19:55	25 ℃
122	Thu 7/3/24 20:05	25 ℃
123	Thu 7/3/24 20:15	25 ℃
124	Thu 7/3/24 20:25	25 ℃
125	Thu 7/3/24 20:35	25 ℃
126	Thu 7/3/24 20:45	25 ℃
127	Thu 7/3/24 20:55	25 ℃
128	Thu 7/3/24 21:05	25 ℃
129	Thu 7/3/24 21:15	25 ℃
130	Thu 7/3/24 21:25	25.1 ℃
131	Thu 7/3/24 21:35	25 ℃
132	Thu 7/3/24 21:45	25.1 ℃
133	Thu 7/3/24 21:55	25.1 ℃
134	Thu 7/3/24 22:05	25.1 ℃
135	Thu 7/3/24 22:15	25.1 ℃
136	Thu 7/3/24 22:25	25.1 ℃
137	Thu 7/3/24 22:35	25.1 ℃
138	Thu 7/3/24 22:45	25.1 ℃
139	Thu 7/3/24 22:55	25.1 ℃
140	Thu 7/3/24 23:05	25.1 ℃
141	Thu 7/3/24 23:15	25.1 ℃
142	Thu 7/3/24 23:25	25.2 ℃
143	Thu 7/3/24 23:35	25.1 ℃
144	Thu 7/3/24 23:45	25.2 ℃
145	Thu 7/3/24 23:55	25.2 ℃
146	Fri 8/3/24 0:05	25.2 ℃
147	Fri 8/3/24 0:15	25.2 ℃
148	Fri 8/3/24 0:25	25.2 ℃
149	Fri 8/3/24 0:35	25.2 ℃
150	Fri 8/3/24 0:45	25.3 ℃
151	Fri 8/3/24 0:55	25.2 ℃
152	Fri 8/3/24 1:05	25.2 ℃
153	Fri 8/3/24 1:15	25.2 ℃
154	Fri 8/3/24 1:25	25.2 ℃
155	Fri 8/3/24 1:35	25.2 ℃
156	Fri 8/3/24 1:45	25.2 ℃
157	Fri 8/3/24 1:55	25.2 ℃
158	Fri 8/3/24 2:05	25.2 ℃
159	Fri 8/3/24 2:15	25.2 ℃
160	Fri 8/3/24 2:25	25.2 ℃
161	Fri 8/3/24 2:35	25.2 ℃
162	Fri 8/3/24 2:45	25.2 ℃
163	Fri 8/3/24 2:55	25.2 ℃
164	Fri 8/3/24 3:05	25.2 ℃
165	Fri 8/3/24 3:15	25.2 ℃
166	Fri 8/3/24 3:25	25.2 ℃
167	Fri 8/3/24 3:35	25.2 ℃
168	Fri 8/3/24 3:45	25.2 ℃
169	Fri 8/3/24 3:55	25.2 ℃
170	Fri 8/3/24 4:05	25.2 ℃
171	Fri 8/3/24 4:15	25.2 ℃
172	Fri 8/3/24 4:25	25.2 ℃
173	Fri 8/3/24 4:35	25.2 ℃
174	Fri 8/3/24 4:45	25.3 ℃
175	Fri 8/3/24 4:55	25.2 ℃
176	Fri 8/3/24 5:05	25.2 ℃
177	Fri 8/3/24 5:15	25.2 ℃
178	Fri 8/3/24 5:25	25.2 ℃
179	Fri 8/3/24 5:35	25.2 ℃
180	Fri 8/3/24 5:45	25.3 ℃
181	Fri 8/3/24 5:55	25.2 ℃
182	Fri 8/3/24 6:05	25.3 ℃
183	Fri 8/3/24 6:15	25.2 ℃
184	Fri 8/3/24 6:25	25.3 ℃
185	Fri 8/3/24 6:35	25.3 ℃
186	Fri 8/3/24 6:45	25.3 ℃
187	Fri 8/3/24 6:55	25.4 ℃
188	Fri 8/3/24 7:05	25.4 ℃
189	Fri 8/3/24 7:15	25.4 ℃
190	Fri 8/3/24 7:25	25.5 ℃
191	Fri 8/3/24 7:35	25.4 ℃
192	Fri 8/3/24 7:45	25.5 ℃
193	Fri 8/3/24 7:55	25.4 ℃
194	Fri 8/3/24 8:05	25.5 ℃
195	Fri 8/3/24 8:15	25.4 ℃
196	Fri 8/3/24 8:25	25.5 ℃
197	Fri 8/3/24 8:35	25.4 ℃
198	Fri 8/3/24 8:45	25.5 ℃
199	Fri 8/3/24 8:55	25.4 ℃
200	Fri 8/3/24 9:05	25.5 ℃
201	Fri 8/3/24 9:15	25.4 ℃
202	Fri 8/3/24 9:25	25.5 ℃
203	Fri 8/3/24 9:35	25.4 ℃
204	Fri 8/3/24 9:45	25.5 ℃
205	Fri 8/3/24 9:55	25.4 ℃
206	Fri 8/3/24 10:05	25.5 ℃
207	Fri 8/3/24 10:15	25.4 ℃
208	Fri 8/3/24 10:25	25.5 ℃
209	Fri 8/3/24 10:35	25.4 ℃
210	Fri 8/3/24 10:45	25.4 ℃
211	Fri 8/3/24 10:55	25.5 ℃
212	Fri 8/3/24 11:05	25.4 ℃
213	Fri 8/3/24 11:15	25.5 ℃
214	Fri 8/3/24 11:25	25.4 ℃
215	Fri 8/3/24 11:35	25.5 ℃
216	Fri 8/3/24 11:45	25.4 ℃
217	Fri 8/3/24 11:55	25.5 ℃
218	Fri 8/3/24 12:05	25.4 ℃
219	Fri 8/3/24 12:15	25.5 ℃
220	Fri 8/3/24 12:25	25.4 ℃
221	Fri 8/3/24 12:35	25.5 ℃
222	Fri 8/3/24 12:45	25.4 ℃
223	Fri 8/3/24 12:55	25.5 ℃
224	Fri 8/3/24 13:05	25.4 ℃
225	Fri 8/3/24 13:15	25.5 ℃
226	Fri 8/3/24 13:25	25.4 ℃
227	Fri 8/3/24 13:35	25.5 ℃
228	Fri 8/3/24 13:45	25.4 ℃
229	Fri 8/3/24 13:55	25.5 ℃
230	Fri 8/3/24 14:05	25.4 ℃
231	Fri 8/3/24 14:15	25.5 ℃
232	Fri 8/3/24 14:25	25.4 ℃
233	Fri 8/3/24 14:35	25.5 ℃
234	Fri 8/3/24 14:45	25.4 ℃
235	Fri 8/3/24 14:55	25.5 ℃
236	Fri 8/3/24 15:05	25.4 ℃
237	Fri 8/3/24 15:15	25.5 ℃
238	Fri 8/3/24 15:25	25.4 ℃
239	Fri 8/3/24 15:35	25.5 ℃
240	Fri 8/3/24 15:45	25.4 ℃
241	Fri 8/3/24 15:55	25.5 ℃
242	Fri 8/3/24 16:05	25.4 ℃
243	Fri 8/3/24 16:15	25.5 ℃
244	Fri 8/3/24 16:25	25.4 ℃
245	Fri 8/3/24 16:35	25.5 ℃
246	Fri 8/3/24 16:45	25.4 ℃
247	Fri 8/3/24 16:55	25.5 ℃
248	Fri 8/3/24 17:05	25.4 ℃
249	Fri 8/3/24 17:15	25.4 ℃
250	Fri 8/3/24 17:25	25.5 ℃
251	Fri 8/3/24 17:35	25.4 ℃
252	Fri 8/3/24 17:45	25.5 ℃
253	Fri 8/3/24 17:55	25.4 ℃
254	Fri 8/3/24 18:05	25.5 ℃
255	Fri 8/3/24 18:15	25.4 ℃
256	Fri 8/3/24 18:25	25.5 ℃
257	Fri 8/3/24 18:35	25.4 ℃
258	Fri 8/3/24 18:45	25.5 ℃
259	Fri 8/3/24 18:55	25.4 ℃
260	Fri 8/3/24 19:05	25.5 ℃
261	Fri 8/3/24 19:15	25.4 ℃
262	Fri 8/3/24 19:25	25.5 ℃
263	Fri 8/3/24 19:35	25.4 ℃
264	Fri 8/3/24 19:45	25.5 ℃
265	Fri 8/3/24 19:55	25.4 ℃
266	Fri 8/3/24 20:05	25.5 ℃
267	Fri 8/3/24 20:15	25.4 ℃
268	Fri 8/3/24 20:25	25.5 ℃
269	Fri 8/3/24 20:35	25.4 ℃
270	Fri 8/3/24 20:45	25.5 ℃
271	Fri 8/3/24 20:55	25.4 ℃
272	Fri 8/3/24 21:05	25.5 ℃
273	Fri 8/3/24 21:15	25.4 ℃
274	Fri 8/3/24 21:25	25.5 ℃
275	Fri 8/3/24 21:35	25.4 ℃
276	Fri 8/3/24 21:45	25.5 ℃
277	Fri 8/3/24 21:55	25.4 ℃
278	Fri 8/3/24 22:05	25.5 ℃
279	Fri 8/3/24 22:15	25.4 ℃
280	Fri 8/3/24 22:25	25.5 ℃
281	Fri 8/3/24 22:35	25.4 ℃
282	Fri 8/3/24 22:45	25.5 ℃
283	Fri 8/3/24 22:55	25.4 ℃
284	Fri 8/3/24 23:05	25.5 ℃
285	Fri 8/3/24 23:15	25.4 ℃
286	Fri 8/3/24 23:25	25.5 ℃
287	Fri 8/3/24 23:35	25.4 ℃
288	Fri 8/3/24 23:45	25.4 ℃
289	Fri 8/3/24 23:55	25.5 ℃
290	Sat 9/3/24 0:05	25.4 ℃
291	Sat 9/3/24 0:15	25.5 ℃
292	Sat 9/3/24 0:25	25.4 ℃
293	Sat 9/3/24 0:35	25.5 ℃
294	Sat 9/3/24 0:45	25.4 ℃
295	Sat 9/3/24 0:55	25.5 ℃
296	Sat 9/3/24 1:05	25.4 ℃
297	Sat 9/3/24 1:15	25.5 ℃
298	Sat 9/3/24 1:25	25.4 ℃
299	Sat 9/3/24 1:35	25.5 ℃
300	Sat 9/3/24 1:45	25.4 ℃
301	Sat 9/3/24 1:55	25.5 ℃
302	Sat 9/3/24 2:05	25.4 ℃
303	Sat 9/3/24 2:15	25.5 ℃
304	Sat 9/3/24 2:25	25.4 ℃
305	Sat 9/3/24 2:35	25.5 ℃
306	Sat 9/3/24 2:45	25.4 ℃
307	Sat 9/3/24 2:55	25.5 ℃
308	Sat 9/3/24 3:05	25.4 ℃
309	Sat 9/3/24 3:15	25.5 ℃
310	Sat 9/3/24 3:25	25.4 ℃
311	Sat 9/3/24 3:35	25.5 ℃
312	Sat 9/3/24 3:45	25.4 ℃
313	Sat 9/3/24 3:55	25.5 ℃
314	Sat 9/3/24 4:05	25.4 ℃
315	Sat 9/3/24 4:15	25.5 ℃
316	Sat 9/3/24 4:25	25.4 ℃
317	Sat 9/3/24 4:35	25.5 ℃
318	Sat 9/3/24 4:45	25.4 ℃
319	Sat 9/3/24 4:55	25.5 ℃
320	Sat 9/3/24 5:05	25.4 ℃
321	Sat 9/3/24 5:15	25.5 ℃
322	Sat 9/3/24 5:25	25.4 ℃
323	Sat 9/3/24 5:35	25.5 ℃
324	Sat 9/3/24 5:45	25.4 ℃
325	Sat 9/3/24 5:55	25.5 ℃
326	Sat 9/3/24 6:05	25.4 ℃
327	Sat 9/3/24 6:15	25.5 ℃
328	Sat 9/3/24 6:25	25.4 ℃
329	Sat 9/3/24 6:35	25.5 ℃
330	Sat 9/3/24 6:45	25.4 ℃
331	Sat 9/3/24 6:55	25.5 ℃
332	Sat 9/3/24 7:05	25.4 ℃
333	Sat 9/3/24 7:15	25.5 ℃
334	Sat 9/3/24 7:25	25.4 ℃
335	Sat 9/3/24 7:35	25.5 ℃
336	Sat 9/3/24 7:45	25.4 ℃
337	Sat 9/3/24 7:55	25.5 ℃
338	Sat 9/3/24 8:05	25.4 ℃
339	Sat 9/3/24 8:15	25.5 ℃
340	Sat 9/3/24 8:25	25.4 ℃
341	Sat 9/3/24 8:35	25.5 ℃
342	Sat 9/3/24 8:45	25.4 ℃
343	Sat 9/3/24 8:55	25.5 ℃
344	Sat 9/3/24 9:05	25.4 ℃
345	Sat 9/3/24 9:15	25.5 ℃
346	Sat 9/3/24 9:25	25.4 ℃
347	Sat 9/3/24 9:35	25.5 ℃
348	Sat 9/3/24 9:45	25.4 ℃
349	Sat 9/3/24 9:55	25.5 ℃
350	Sat 9/3/24 10:05	25.4 ℃
jappi

24 03 16.xlsm
	I	J
1	Time	Temperature
2	Thu 7/3/24 8:06	26.1 ℃
3	Thu 7/3/24 9:05	26.7 ℃
4	Thu 7/3/24 10:05	24.4 ℃
5	Thu 7/3/24 11:05	23.2 ℃
6	Thu 7/3/24 12:05	23.3 ℃
7	Thu 7/3/24 13:05	23.1 ℃
8	Thu 7/3/24 14:05	23.1 ℃
9	Thu 7/3/24 15:05	23 ℃
10	Thu 7/3/24 16:05	22.8 ℃
11	Thu 7/3/24 17:05	23.7 ℃
12	Fri 8/3/24 8:05	25.5 ℃
13	Fri 8/3/24 9:05	25.5 ℃
14	Fri 8/3/24 10:05	25.5 ℃
15	Fri 8/3/24 11:05	25.4 ℃
16	Fri 8/3/24 12:05	25.4 ℃
17	Fri 8/3/24 13:05	25.4 ℃
18	Fri 8/3/24 14:05	25.4 ℃
19	Fri 8/3/24 15:05	25.4 ℃
20	Fri 8/3/24 16:05	25.4 ℃
21	Fri 8/3/24 17:05	25.4 ℃
22
23
jappi

Cell Formulas
Range	Formula
I2:O21	I2	=LET(a,A2:A350,h,HOUR(a),m,MINUTE(a),FILTER(A2:G350,(m<10)*(h>=8)*(h<=17+(m=0))*(WEEKDAY(a,2)<6),""))
Dynamic array formulas.

 

[jappi]

Looking at the thread again it also seems a little strange that you asked for weekends to be excluded yet the only sample data we have consists of nothing but weekend.

I am also unsure if the data is always produced for the 06, 16, 26 etc minutes (ignoring the change to 05 for the moment) or on another occasion the minutes might be
03, 13, 23, etc or
00, 10, 20, etc

If the minutes end with anything other than zero, @awoohaw your formula in post 17 will return times after 6pm
If the minutes could end with zero then @Cubist your formula in post 18 (& awoohaw's modification in post 19) would omit the exact time of 6pm (I know this possibility is not mentioned by the OP so my comment on this may not be relevant.)


True, but you haven't applied it correctly. You formula excludes Sunday only.

This is my attempt. I have expanded the sample data to include some week days and some weekend days.
Pending the outcome of the change from 06 minutes to 05 minutes, I have tried to report the first available time in each of the relevant hours. So I have both 06 and 05 in my results.
I have only shown two columns from sample data and results otherwise I exceed the XL2BB and/or forum limits.

24 03 16.xlsm
	A	B
1	Time	Temperature
2	Thu 7/3/24 0:06	24.5 ℃
3	Thu 7/3/24 0:16	24.5 ℃
4	Thu 7/3/24 0:26	24.5 ℃
5	Thu 7/3/24 0:36	24.5 ℃
6	Thu 7/3/24 0:46	24.6 ℃
7	Thu 7/3/24 0:56	24.5 ℃
8	Thu 7/3/24 1:06	24.5 ℃
9	Thu 7/3/24 1:16	24.5 ℃
10	Thu 7/3/24 1:26	24.5 ℃
11	Thu 7/3/24 1:36	24.5 ℃
12	Thu 7/3/24 1:46	24.5 ℃
13	Thu 7/3/24 1:56	24.5 ℃
14	Thu 7/3/24 2:06	24.5 ℃
15	Thu 7/3/24 2:16	24.5 ℃
16	Thu 7/3/24 2:26	24.5 ℃
17	Thu 7/3/24 2:36	24.5 ℃
18	Thu 7/3/24 2:46	24.6 ℃
19	Thu 7/3/24 2:56	24.6 ℃
20	Thu 7/3/24 3:06	24.6 ℃
21	Thu 7/3/24 3:16	24.5 ℃
22	Thu 7/3/24 3:26	24.5 ℃
23	Thu 7/3/24 3:36	24.6 ℃
24	Thu 7/3/24 3:46	24.6 ℃
25	Thu 7/3/24 3:56	24.6 ℃
26	Thu 7/3/24 4:06	24.6 ℃
27	Thu 7/3/24 4:16	24.5 ℃
28	Thu 7/3/24 4:26	24.6 ℃
29	Thu 7/3/24 4:36	24.6 ℃
30	Thu 7/3/24 4:46	24.6 ℃
31	Thu 7/3/24 4:56	24.6 ℃
32	Thu 7/3/24 5:06	24.6 ℃
33	Thu 7/3/24 5:16	24.6 ℃
34	Thu 7/3/24 5:26	24.6 ℃
35	Thu 7/3/24 5:36	24.6 ℃
36	Thu 7/3/24 5:46	24.6 ℃
37	Thu 7/3/24 5:56	24.6 ℃
38	Thu 7/3/24 6:06	24.6 ℃
39	Thu 7/3/24 6:16	24.7 ℃
40	Thu 7/3/24 6:26	24.8 ℃
41	Thu 7/3/24 6:36	24.9 ℃
42	Thu 7/3/24 6:46	25 ℃
43	Thu 7/3/24 6:56	25.1 ℃
44	Thu 7/3/24 7:06	25.3 ℃
45	Thu 7/3/24 7:16	25.4 ℃
46	Thu 7/3/24 7:26	25.5 ℃
47	Thu 7/3/24 7:36	25.7 ℃
48	Thu 7/3/24 7:46	25.8 ℃
49	Thu 7/3/24 7:56	25.9 ℃
50	Thu 7/3/24 8:06	26.1 ℃
51	Thu 7/3/24 8:16	26.2 ℃
52	Thu 7/3/24 8:26	26.3 ℃
53	Thu 7/3/24 8:36	26.4 ℃
54	Thu 7/3/24 8:46	26.5 ℃
55	Thu 7/3/24 8:55	26.6 ℃
56	Thu 7/3/24 9:05	26.7 ℃
57	Thu 7/3/24 9:16	26.4 ℃
58	Thu 7/3/24 9:25	25.8 ℃
59	Thu 7/3/24 9:35	25.3 ℃
60	Thu 7/3/24 9:45	25 ℃
61	Thu 7/3/24 9:55	24.7 ℃
62	Thu 7/3/24 10:05	24.4 ℃
63	Thu 7/3/24 10:15	24.2 ℃
64	Thu 7/3/24 10:25	23.9 ℃
65	Thu 7/3/24 10:35	23.7 ℃
66	Thu 7/3/24 10:45	23.5 ℃
67	Thu 7/3/24 10:55	23.3 ℃
68	Thu 7/3/24 11:05	23.2 ℃
69	Thu 7/3/24 11:15	23.1 ℃
70	Thu 7/3/24 11:25	23.1 ℃
71	Thu 7/3/24 11:35	23.2 ℃
72	Thu 7/3/24 11:45	23.3 ℃
73	Thu 7/3/24 11:55	23.4 ℃
74	Thu 7/3/24 12:05	23.3 ℃
75	Thu 7/3/24 12:15	23.3 ℃
76	Thu 7/3/24 12:25	23.3 ℃
77	Thu 7/3/24 12:35	23.1 ℃
78	Thu 7/3/24 12:45	23.2 ℃
79	Thu 7/3/24 12:55	23.2 ℃
80	Thu 7/3/24 13:05	23.1 ℃
81	Thu 7/3/24 13:15	23.1 ℃
82	Thu 7/3/24 13:25	23.1 ℃
83	Thu 7/3/24 13:35	23 ℃
84	Thu 7/3/24 13:45	23.1 ℃
85	Thu 7/3/24 13:55	23.1 ℃
86	Thu 7/3/24 14:05	23.1 ℃
87	Thu 7/3/24 14:15	23.1 ℃
88	Thu 7/3/24 14:25	23 ℃
89	Thu 7/3/24 14:35	23 ℃
90	Thu 7/3/24 14:45	23 ℃
91	Thu 7/3/24 14:55	23 ℃
92	Thu 7/3/24 15:05	23 ℃
93	Thu 7/3/24 15:15	23 ℃
94	Thu 7/3/24 15:25	23 ℃
95	Thu 7/3/24 15:35	23.1 ℃
96	Thu 7/3/24 15:45	23 ℃
97	Thu 7/3/24 15:55	22.8 ℃
98	Thu 7/3/24 16:05	22.8 ℃
99	Thu 7/3/24 16:15	22.7 ℃
100	Thu 7/3/24 16:25	22.7 ℃
101	Thu 7/3/24 16:35	22.7 ℃
102	Thu 7/3/24 16:45	22.9 ℃
103	Thu 7/3/24 16:55	23.3 ℃
104	Thu 7/3/24 17:05	23.7 ℃
105	Thu 7/3/24 17:15	23.9 ℃
106	Thu 7/3/24 17:25	24 ℃
107	Thu 7/3/24 17:35	24.2 ℃
108	Thu 7/3/24 17:45	24.4 ℃
109	Thu 7/3/24 17:55	24.5 ℃
110	Thu 7/3/24 18:05	24.6 ℃
111	Thu 7/3/24 18:15	24.6 ℃
112	Thu 7/3/24 18:25	24.7 ℃
113	Thu 7/3/24 18:35	24.7 ℃
114	Thu 7/3/24 18:45	24.8 ℃
115	Thu 7/3/24 18:55	24.8 ℃
116	Thu 7/3/24 19:05	24.9 ℃
117	Thu 7/3/24 19:15	24.9 ℃
118	Thu 7/3/24 19:25	24.9 ℃
119	Thu 7/3/24 19:35	24.9 ℃
120	Thu 7/3/24 19:45	25 ℃
121	Thu 7/3/24 19:55	25 ℃
122	Thu 7/3/24 20:05	25 ℃
123	Thu 7/3/24 20:15	25 ℃
124	Thu 7/3/24 20:25	25 ℃
125	Thu 7/3/24 20:35	25 ℃
126	Thu 7/3/24 20:45	25 ℃
127	Thu 7/3/24 20:55	25 ℃
128	Thu 7/3/24 21:05	25 ℃
129	Thu 7/3/24 21:15	25 ℃
130	Thu 7/3/24 21:25	25.1 ℃
131	Thu 7/3/24 21:35	25 ℃
132	Thu 7/3/24 21:45	25.1 ℃
133	Thu 7/3/24 21:55	25.1 ℃
134	Thu 7/3/24 22:05	25.1 ℃
135	Thu 7/3/24 22:15	25.1 ℃
136	Thu 7/3/24 22:25	25.1 ℃
137	Thu 7/3/24 22:35	25.1 ℃
138	Thu 7/3/24 22:45	25.1 ℃
139	Thu 7/3/24 22:55	25.1 ℃
140	Thu 7/3/24 23:05	25.1 ℃
141	Thu 7/3/24 23:15	25.1 ℃
142	Thu 7/3/24 23:25	25.2 ℃
143	Thu 7/3/24 23:35	25.1 ℃
144	Thu 7/3/24 23:45	25.2 ℃
145	Thu 7/3/24 23:55	25.2 ℃
146	Fri 8/3/24 0:05	25.2 ℃
147	Fri 8/3/24 0:15	25.2 ℃
148	Fri 8/3/24 0:25	25.2 ℃
149	Fri 8/3/24 0:35	25.2 ℃
150	Fri 8/3/24 0:45	25.3 ℃
151	Fri 8/3/24 0:55	25.2 ℃
152	Fri 8/3/24 1:05	25.2 ℃
153	Fri 8/3/24 1:15	25.2 ℃
154	Fri 8/3/24 1:25	25.2 ℃
155	Fri 8/3/24 1:35	25.2 ℃
156	Fri 8/3/24 1:45	25.2 ℃
157	Fri 8/3/24 1:55	25.2 ℃
158	Fri 8/3/24 2:05	25.2 ℃
159	Fri 8/3/24 2:15	25.2 ℃
160	Fri 8/3/24 2:25	25.2 ℃
161	Fri 8/3/24 2:35	25.2 ℃
162	Fri 8/3/24 2:45	25.2 ℃
163	Fri 8/3/24 2:55	25.2 ℃
164	Fri 8/3/24 3:05	25.2 ℃
165	Fri 8/3/24 3:15	25.2 ℃
166	Fri 8/3/24 3:25	25.2 ℃
167	Fri 8/3/24 3:35	25.2 ℃
168	Fri 8/3/24 3:45	25.2 ℃
169	Fri 8/3/24 3:55	25.2 ℃
170	Fri 8/3/24 4:05	25.2 ℃
171	Fri 8/3/24 4:15	25.2 ℃
172	Fri 8/3/24 4:25	25.2 ℃
173	Fri 8/3/24 4:35	25.2 ℃
174	Fri 8/3/24 4:45	25.3 ℃
175	Fri 8/3/24 4:55	25.2 ℃
176	Fri 8/3/24 5:05	25.2 ℃
177	Fri 8/3/24 5:15	25.2 ℃
178	Fri 8/3/24 5:25	25.2 ℃
179	Fri 8/3/24 5:35	25.2 ℃
180	Fri 8/3/24 5:45	25.3 ℃
181	Fri 8/3/24 5:55	25.2 ℃
182	Fri 8/3/24 6:05	25.3 ℃
183	Fri 8/3/24 6:15	25.2 ℃
184	Fri 8/3/24 6:25	25.3 ℃
185	Fri 8/3/24 6:35	25.3 ℃
186	Fri 8/3/24 6:45	25.3 ℃
187	Fri 8/3/24 6:55	25.4 ℃
188	Fri 8/3/24 7:05	25.4 ℃
189	Fri 8/3/24 7:15	25.4 ℃
190	Fri 8/3/24 7:25	25.5 ℃
191	Fri 8/3/24 7:35	25.4 ℃
192	Fri 8/3/24 7:45	25.5 ℃
193	Fri 8/3/24 7:55	25.4 ℃
194	Fri 8/3/24 8:05	25.5 ℃
195	Fri 8/3/24 8:15	25.4 ℃
196	Fri 8/3/24 8:25	25.5 ℃
197	Fri 8/3/24 8:35	25.4 ℃
198	Fri 8/3/24 8:45	25.5 ℃
199	Fri 8/3/24 8:55	25.4 ℃
200	Fri 8/3/24 9:05	25.5 ℃
201	Fri 8/3/24 9:15	25.4 ℃
202	Fri 8/3/24 9:25	25.5 ℃
203	Fri 8/3/24 9:35	25.4 ℃
204	Fri 8/3/24 9:45	25.5 ℃
205	Fri 8/3/24 9:55	25.4 ℃
206	Fri 8/3/24 10:05	25.5 ℃
207	Fri 8/3/24 10:15	25.4 ℃
208	Fri 8/3/24 10:25	25.5 ℃
209	Fri 8/3/24 10:35	25.4 ℃
210	Fri 8/3/24 10:45	25.4 ℃
211	Fri 8/3/24 10:55	25.5 ℃
212	Fri 8/3/24 11:05	25.4 ℃
213	Fri 8/3/24 11:15	25.5 ℃
214	Fri 8/3/24 11:25	25.4 ℃
215	Fri 8/3/24 11:35	25.5 ℃
216	Fri 8/3/24 11:45	25.4 ℃
217	Fri 8/3/24 11:55	25.5 ℃
218	Fri 8/3/24 12:05	25.4 ℃
219	Fri 8/3/24 12:15	25.5 ℃
220	Fri 8/3/24 12:25	25.4 ℃
221	Fri 8/3/24 12:35	25.5 ℃
222	Fri 8/3/24 12:45	25.4 ℃
223	Fri 8/3/24 12:55	25.5 ℃
224	Fri 8/3/24 13:05	25.4 ℃
225	Fri 8/3/24 13:15	25.5 ℃
226	Fri 8/3/24 13:25	25.4 ℃
227	Fri 8/3/24 13:35	25.5 ℃
228	Fri 8/3/24 13:45	25.4 ℃
229	Fri 8/3/24 13:55	25.5 ℃
230	Fri 8/3/24 14:05	25.4 ℃
231	Fri 8/3/24 14:15	25.5 ℃
232	Fri 8/3/24 14:25	25.4 ℃
233	Fri 8/3/24 14:35	25.5 ℃
234	Fri 8/3/24 14:45	25.4 ℃
235	Fri 8/3/24 14:55	25.5 ℃
236	Fri 8/3/24 15:05	25.4 ℃
237	Fri 8/3/24 15:15	25.5 ℃
238	Fri 8/3/24 15:25	25.4 ℃
239	Fri 8/3/24 15:35	25.5 ℃
240	Fri 8/3/24 15:45	25.4 ℃
241	Fri 8/3/24 15:55	25.5 ℃
242	Fri 8/3/24 16:05	25.4 ℃
243	Fri 8/3/24 16:15	25.5 ℃
244	Fri 8/3/24 16:25	25.4 ℃
245	Fri 8/3/24 16:35	25.5 ℃
246	Fri 8/3/24 16:45	25.4 ℃
247	Fri 8/3/24 16:55	25.5 ℃
248	Fri 8/3/24 17:05	25.4 ℃
249	Fri 8/3/24 17:15	25.4 ℃
250	Fri 8/3/24 17:25	25.5 ℃
251	Fri 8/3/24 17:35	25.4 ℃
252	Fri 8/3/24 17:45	25.5 ℃
253	Fri 8/3/24 17:55	25.4 ℃
254	Fri 8/3/24 18:05	25.5 ℃
255	Fri 8/3/24 18:15	25.4 ℃
256	Fri 8/3/24 18:25	25.5 ℃
257	Fri 8/3/24 18:35	25.4 ℃
258	Fri 8/3/24 18:45	25.5 ℃
259	Fri 8/3/24 18:55	25.4 ℃
260	Fri 8/3/24 19:05	25.5 ℃
261	Fri 8/3/24 19:15	25.4 ℃
262	Fri 8/3/24 19:25	25.5 ℃
263	Fri 8/3/24 19:35	25.4 ℃
264	Fri 8/3/24 19:45	25.5 ℃
265	Fri 8/3/24 19:55	25.4 ℃
266	Fri 8/3/24 20:05	25.5 ℃
267	Fri 8/3/24 20:15	25.4 ℃
268	Fri 8/3/24 20:25	25.5 ℃
269	Fri 8/3/24 20:35	25.4 ℃
270	Fri 8/3/24 20:45	25.5 ℃
271	Fri 8/3/24 20:55	25.4 ℃
272	Fri 8/3/24 21:05	25.5 ℃
273	Fri 8/3/24 21:15	25.4 ℃
274	Fri 8/3/24 21:25	25.5 ℃
275	Fri 8/3/24 21:35	25.4 ℃
276	Fri 8/3/24 21:45	25.5 ℃
277	Fri 8/3/24 21:55	25.4 ℃
278	Fri 8/3/24 22:05	25.5 ℃
279	Fri 8/3/24 22:15	25.4 ℃
280	Fri 8/3/24 22:25	25.5 ℃
281	Fri 8/3/24 22:35	25.4 ℃
282	Fri 8/3/24 22:45	25.5 ℃
283	Fri 8/3/24 22:55	25.4 ℃
284	Fri 8/3/24 23:05	25.5 ℃
285	Fri 8/3/24 23:15	25.4 ℃
286	Fri 8/3/24 23:25	25.5 ℃
287	Fri 8/3/24 23:35	25.4 ℃
288	Fri 8/3/24 23:45	25.4 ℃
289	Fri 8/3/24 23:55	25.5 ℃
290	Sat 9/3/24 0:05	25.4 ℃
291	Sat 9/3/24 0:15	25.5 ℃
292	Sat 9/3/24 0:25	25.4 ℃
293	Sat 9/3/24 0:35	25.5 ℃
294	Sat 9/3/24 0:45	25.4 ℃
295	Sat 9/3/24 0:55	25.5 ℃
296	Sat 9/3/24 1:05	25.4 ℃
297	Sat 9/3/24 1:15	25.5 ℃
298	Sat 9/3/24 1:25	25.4 ℃
299	Sat 9/3/24 1:35	25.5 ℃
300	Sat 9/3/24 1:45	25.4 ℃
301	Sat 9/3/24 1:55	25.5 ℃
302	Sat 9/3/24 2:05	25.4 ℃
303	Sat 9/3/24 2:15	25.5 ℃
304	Sat 9/3/24 2:25	25.4 ℃
305	Sat 9/3/24 2:35	25.5 ℃
306	Sat 9/3/24 2:45	25.4 ℃
307	Sat 9/3/24 2:55	25.5 ℃
308	Sat 9/3/24 3:05	25.4 ℃
309	Sat 9/3/24 3:15	25.5 ℃
310	Sat 9/3/24 3:25	25.4 ℃
311	Sat 9/3/24 3:35	25.5 ℃
312	Sat 9/3/24 3:45	25.4 ℃
313	Sat 9/3/24 3:55	25.5 ℃
314	Sat 9/3/24 4:05	25.4 ℃
315	Sat 9/3/24 4:15	25.5 ℃
316	Sat 9/3/24 4:25	25.4 ℃
317	Sat 9/3/24 4:35	25.5 ℃
318	Sat 9/3/24 4:45	25.4 ℃
319	Sat 9/3/24 4:55	25.5 ℃
320	Sat 9/3/24 5:05	25.4 ℃
321	Sat 9/3/24 5:15	25.5 ℃
322	Sat 9/3/24 5:25	25.4 ℃
323	Sat 9/3/24 5:35	25.5 ℃
324	Sat 9/3/24 5:45	25.4 ℃
325	Sat 9/3/24 5:55	25.5 ℃
326	Sat 9/3/24 6:05	25.4 ℃
327	Sat 9/3/24 6:15	25.5 ℃
328	Sat 9/3/24 6:25	25.4 ℃
329	Sat 9/3/24 6:35	25.5 ℃
330	Sat 9/3/24 6:45	25.4 ℃
331	Sat 9/3/24 6:55	25.5 ℃
332	Sat 9/3/24 7:05	25.4 ℃
333	Sat 9/3/24 7:15	25.5 ℃
334	Sat 9/3/24 7:25	25.4 ℃
335	Sat 9/3/24 7:35	25.5 ℃
336	Sat 9/3/24 7:45	25.4 ℃
337	Sat 9/3/24 7:55	25.5 ℃
338	Sat 9/3/24 8:05	25.4 ℃
339	Sat 9/3/24 8:15	25.5 ℃
340	Sat 9/3/24 8:25	25.4 ℃
341	Sat 9/3/24 8:35	25.5 ℃
342	Sat 9/3/24 8:45	25.4 ℃
343	Sat 9/3/24 8:55	25.5 ℃
344	Sat 9/3/24 9:05	25.4 ℃
345	Sat 9/3/24 9:15	25.5 ℃
346	Sat 9/3/24 9:25	25.4 ℃
347	Sat 9/3/24 9:35	25.5 ℃
348	Sat 9/3/24 9:45	25.4 ℃
349	Sat 9/3/24 9:55	25.5 ℃
350	Sat 9/3/24 10:05	25.4 ℃
jappi

24 03 16.xlsm
	I	J
1	Time	Temperature
2	Thu 7/3/24 8:06	26.1 ℃
3	Thu 7/3/24 9:05	26.7 ℃
4	Thu 7/3/24 10:05	24.4 ℃
5	Thu 7/3/24 11:05	23.2 ℃
6	Thu 7/3/24 12:05	23.3 ℃
7	Thu 7/3/24 13:05	23.1 ℃
8	Thu 7/3/24 14:05	23.1 ℃
9	Thu 7/3/24 15:05	23 ℃
10	Thu 7/3/24 16:05	22.8 ℃
11	Thu 7/3/24 17:05	23.7 ℃
12	Fri 8/3/24 8:05	25.5 ℃
13	Fri 8/3/24 9:05	25.5 ℃
14	Fri 8/3/24 10:05	25.5 ℃
15	Fri 8/3/24 11:05	25.4 ℃
16	Fri 8/3/24 12:05	25.4 ℃
17	Fri 8/3/24 13:05	25.4 ℃
18	Fri 8/3/24 14:05	25.4 ℃
19	Fri 8/3/24 15:05	25.4 ℃
20	Fri 8/3/24 16:05	25.4 ℃
21	Fri 8/3/24 17:05	25.4 ℃
22
23
jappi

Cell Formulas
Range	Formula
I2:O21	I2	=LET(a,A2:A350,h,HOUR(a),m,MINUTE(a),FILTER(A2:G350,(m<10)*(h>=8)*(h<=17+(m=0))*(WEEKDAY(a,2)<6),""))
Dynamic array formulas.

Sorry for the radio silence. Have been on long haul flights. All the comments are right about the data changing in terms of minute intervals. I hadn't seen that before. I've taken a 3 month sample and applied the most recent formula and all initial checks look good. I'll work through this a little more but seems good for now. Thanks again everyone. I will come back an mark as a solution shortly once I have it all locked down.

 

[Peter_SSs]

OK, cheers. Hope it works out for you.

 

[Peter_SSs]

I will come back an mark as a solution shortly once I have it all locked down.

Cheers, I see that you have returned but the marked post was not the solution post.
The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.