How to get value using VLOOKUP from datetime range?

[pete1229]

I have 2 columns (A and B) which contains the datetime of when the data was obtained and the data value respectively. I attempted to use these columns as the vlookup table array. I want to capture the data in a 30-minute cycle (e.g. 09:00, 09:30, 10:00, 10:30 etc) and insert these values into column F.
For example, if a data was received at 09:15, its value would be returned at 09:00; if it was received at 09:59, its value would be returned at 09:30.

If more than 1 data value was received within the same 30-minute cycle, the values would be summed. For example, if two data values of 0.1 and 0.2 were received at 09:15 and 09:17, the sum value of 0.3 would be returned at 09:00.

If no data was received for any 30-minute cycle, it simply returns a zero.

The dataset I have is over a year's worth, I am open to solutions using cell equations or VBA.

Data obtained	Data value			Datetime	Output	Expected output
28/07/2021 09:35	0.1			28/07/2021 09:00		0
29/07/2021 07:55	0.2			28/07/2021 09:30		0.1
29/07/2021 11:09	0.3			28/07/2021 10:00		0
29/07/2021 11:48	0.4			29/07/2021 10:30		0
29/07/2021 11:59	0.5			29/07/2021 11:00		0.3
30/07/2021 12:46	0.6			29/07/2021 11:30		0.9
30/07/2021 12:53	0.7			30/07/2021 12:00		0
31/07/2021 12:59	0.8			30/07/2021 12:30		2.1
31/07/2021 13:00	0.9			31/07/2021 13:00		0.9

 

[etaf]

how about

Book8
	A	B	C	D	E	F	G
1	Data obtained	Data value			Datetime	Output	Expected output
2	7/28/21 9:35	0.1			7/28/21 9:00	0	0
3	7/29/21 7:55	0.2			7/28/21 9:30	0.1	0.1
4	7/29/21 11:09	0.3			7/28/21 10:00	0	0
5	7/29/21 11:48	0.4			7/29/21 10:30	0	0
6	7/29/21 11:59	0.5			7/29/21 11:00	0.3	0.3
7	7/30/21 12:46	0.6			7/29/21 11:30	0.9	0.9
8	7/30/21 12:53	0.7			7/30/21 12:00	0	0
9	7/31/21 12:59	0.8			7/30/21 12:30	1.3	2.1
10	7/31/21 13:00	0.9			7/31/21 13:00	0.9	0.9
Sheet2

Cell Formulas
Range	Formula
F2:F10	F2	=SUMIFS($B$2:$B$10,$A$2:$A$10,">="&E2,$A$2:$A$10,"<"&E2+TIME(0,29,59))

just need to recheck the 2.1

so
31/07/2021 12:59:00
is not in your 2nd list and not correct

changed 31 to 30th

Book8
	A	B	C	D	E	F	G
1	Data obtained	Data value			Datetime	Output	Expected output
2	7/28/21 9:35	0.1			7/28/21 9:00	0	0
3	7/29/21 7:55	0.2			7/28/21 9:30	0.1	0.1
4	7/29/21 11:09	0.3			7/28/21 10:00	0	0
5	7/29/21 11:48	0.4			7/29/21 10:30	0	0
6	7/29/21 11:59	0.5			7/29/21 11:00	0.3	0.3
7	7/30/21 12:46	0.6			7/29/21 11:30	0.9	0.9
8	7/30/21 12:53	0.7			7/30/21 12:00	0	0
9	7/30/21 12:59	0.8			7/30/21 12:30	2.1	2.1
10	7/31/21 13:00	0.9			7/31/21 13:00	0.9	0.9
Sheet2

Cell Formulas
Range	Formula
F2:F10	F2	=SUMIFS($B$2:$B$10,$A$2:$A$10,">="&E2,$A$2:$A$10,"<"&E2+TIME(0,29,59))

 

[pete1229]

Apologies, the date for the 2.1 output value was indeed incorrect.
Your answer is exactly what I need, absolutely perfect!
Thank you!

 

[etaf]

you are welcome