Trying to find start and end times in a date/time column with a difficult twist.

[renraw9002]

Ok here's the problem. I have a date and time column we'll call column A. Column A has a matching column B where a temperature value is located. Column A+B only record a value every 5 minutes and even then it's not exactly 5 minutes every time. Occasionally it'll be 5 and 13 or 18 seconds, etc.

I have column C with the start date and time, in a matching format to A, and I have column D with the end date and date time of when a ton of processes have obviously started and ended. However column C and D are populated whenever someone records they started or ended that respective process which could be any time at all. Except in that case C and D never bother recording the seconds so it'll at least be on a minute. What I'm trying to find is the lowest and highest temp from column B from between the start and end times of columns C and D... and then do it probably about 900-1000 more times.

 

[renraw9002]

Edit: also even with column B only recording a value every 5 minutes. The start and end times of one process are 2 weeks apart so a value every 5 minutes. It'll be around 4,000 values in column B to search through for the lowest and highest temps. So if excel is not the tool for this job please let me know.

 

[myall_blues]

This should be doable with VBA - can you post some example data (30-40 lines should do it), preferably using XL2BB if you can but in a table is OK if not.

 

[felixstraube]

Would this work?:

Data extends to row 4001.

Book6.xlsx
	A	B	C	D	E	F
1	Datetime	Temp	StartDatetime	EndDatetime	Min	Max
2	01/08/2024 12:01:46	45	01/08/2024 12:01:00	01/08/2024 12:53:00	1	97
3	01/08/2024 12:05:37	14	01/08/2024 15:09:00	01/08/2024 16:09:00	5	94
4	01/08/2024 12:07:58	92	02/08/2024 05:51:07	02/08/2024 07:01:00	2	100
5	01/08/2024 12:09:47	49
6	01/08/2024 12:11:46	40
7	01/08/2024 12:16:06	91
8	01/08/2024 12:17:41	97
9	01/08/2024 12:19:05	60
10	01/08/2024 12:20:44	1
11	01/08/2024 12:24:39	89
12	01/08/2024 12:25:42	52
Sheet2

Cell Formulas
Range	Formula
E2:F4	E2	=LET(d,FILTER($B$2:$B$4001,($A$2:$A$4001>=C2)*($A$2:$A$4001<=D2)), HSTACK(MIN(d),MAX(d)))
Dynamic array formulas.

 

[renraw9002]

This should be doable with VBA - can you post some example data (30-40 lines should do it), preferably using XL2BB if you can but in a table is OK if not.

I am really sorry for the long delay. Work has me going in 5 different directions. Also unfortunately this is a work laptop that the spreadsheet is on and I can't download stuff onto it. Without using xl2bb i don't know how to show a table so here's a pair of photos. The first photo has 30 or so sample batch numbers that have start and end times listed. The second photo is a snippet from one of the excel files that has the temperature readings from a small stretch of time. I have temperature readings from every 5 minutes like the ones shown in the second photo from the beginning of the month all the way back to the middle of 2022. As you can see in the first photo the start and end times are roughly 2 weeks apart so searching a column that has readings from every 5 minutes is a lot of values to search for. I also have probably 350 batches with start and end times to search with.

 

[myall_blues]

I'll give it a go but before I do did @felixstraube's solution work for you?

 

[myall_blues]

Another option. It looks to me like your start and end times are text, hence the 'VALUE' in the formulas, but if that's not the case you can remove it.

Book1
	A	B	C	D	E	F	G	H
1	DateTime	Value		Batch number	Start	End	Min	Max
2	30/04/2024 20:20	36.0815		0001234567	29/04/2024 17:00:00	30/04/2024 23:00:00	36.0815	37.8089
3	30/04/2024 20:14	37.3		0001234568	30/04/2024 04:00:00	30/04/2024 19:00:00	36.2889	37.8089
4	30/04/2024 18:42	36.434
5	30/04/2024 18:11	36.5577
6	30/04/2024 16:07	36.8773
7	30/04/2024 15:42	36.8338
8	30/04/2024 15:01	37.8089
9	30/04/2024 14:28	36.9866
10	30/04/2024 14:21	37.1033
11	30/04/2024 11:19	36.4048
12	30/04/2024 11:01	37.6452
13	30/04/2024 10:36	36.2889
14	30/04/2024 09:49	37.6378
15	30/04/2024 07:25	36.6916
16	30/04/2024 06:05	37.4612
17	30/04/2024 05:26	37.4139
18	30/04/2024 04:50	36.4908
19	30/04/2024 04:48	37.3292
20	30/04/2024 03:57	36.5097
21	30/04/2024 03:42	36.8389
22	30/04/2024 02:16	36.6743
23	30/04/2024 01:28	36.3914
24	30/04/2024 00:34	36.6245
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=MINIFS($B$2:$B$24,$A$2:$A$24,">=" & VALUE($E2),$A$2:$A$24,"<=" &VALUE($F2))
H2:H3	H2	=MAXIFS($B$2:$B$24,$A$2:$A$24,">=" & VALUE($E2),$A$2:$A$24,"<=" &VALUE($F2))

 

[renraw9002]

Would this work?:

Data extends to row 4001.

Book6.xlsx
	A	B	C	D	E	F
1	Datetime	Temp	StartDatetime	EndDatetime	Min	Max
2	01/08/2024 12:01:46	45	01/08/2024 12:01:00	01/08/2024 12:53:00	1	97
3	01/08/2024 12:05:37	14	01/08/2024 15:09:00	01/08/2024 16:09:00	5	94
4	01/08/2024 12:07:58	92	02/08/2024 05:51:07	02/08/2024 07:01:00	2	100
5	01/08/2024 12:09:47	49
6	01/08/2024 12:11:46	40
7	01/08/2024 12:16:06	91
8	01/08/2024 12:17:41	97
9	01/08/2024 12:19:05	60
10	01/08/2024 12:20:44	1
11	01/08/2024 12:24:39	89
12	01/08/2024 12:25:42	52
Sheet2

Cell Formulas
Range	Formula
E2:F4	E2	=LET(d,FILTER($B$2:$B$4001,($A$2:$A$4001>=C2)*($A$2:$A$4001<=D2)), HSTACK(MIN(d),MAX(d)))
Dynamic array formulas.

I think this one is working! I managed to test it on a month's worth of values and they all populated fine. When I get some time I'll verify the values are in fact correct, but a cursory look at things it looks like this was what I needed. Thank you both so much.

 

[felixstraube]

Thanks for the feedback.
Let me know how it goes after checking the results.