How to get sum value from start time and end time?

[pete1229]

This is following on from my previous question where I want to create a half hour time series.
I have a start time, duration and a data value in Columns A, B and C respectively. Similar to my previous question, I want to capture the data that falls during the start time and end time and insert the sum of this data in a 30-minute cycle (e.g. 09:00, 09:30, 10:00, 10:30 etc) in the "Output" column.

For example, if Data_A had a start time was at 09:15 and end time at 10:15, its value would be returned at 09:00, 09:30 and 10:00.

If more than 1 data value was received within the same 30-minute cycle, the values would be summed.
For example, Data_A has a value of 0.1 and Data_B has a value of 0.2.
Data_B has a start time at 09:50 and end time at 10:10. The sum values at 09:00, 09:30 and 10:00 would be 0.1, 0.3 and 0.3 respectively.

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.

test.xlsx
	A	B	C	D	E	F	G	H	I
1	Start time	Duration (hours)	Data value		End time		Datetime	Output	Expected output
2	28/07/2021 09:35	1.0	0.1		28/07/2021 10:35		28/07/2021 09:00		0
3	29/07/2021 07:55	1.5	0.2		29/07/2021 09:25		28/07/2021 09:30		0.2
4	29/07/2021 11:09	0.5	0.3		29/07/2021 11:39		28/07/2021 10:00		0
5	29/07/2021 11:48	0.5	0.4		29/07/2021 12:18		29/07/2021 10:30		0
6	29/07/2021 11:59	1.5	0.5		29/07/2021 13:29		29/07/2021 11:00		0.3
7	30/07/2021 12:46	1.2	0.6		30/07/2021 13:58		29/07/2021 11:30		1.2
8	30/07/2021 12:53	0.8	0.7		30/07/2021 13:41		30/07/2021 12:00		0.9
9	30/07/2021 12:59	0.8	0.8		30/07/2021 13:47		30/07/2021 12:30		2.6
10	30/07/2021 13:00	0.8	0.9		30/07/2021 13:48		30/07/2021 13:00		3.5
11							30/07/2021 13:30		3
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=A2+(B2/24)

 

[pete1229]

From my previously linked question, I tried modifying the following equation to take into account the duration and end time but I'm not getting the expected values.

=SUMIFS($C$2:$C$10,$A$2:$A$10,">="&G2,$A$2:$A$10,"<"&G2+TIME(0,29,59))

 

[pete1229]

There was a mistake in I3 and I4 where the values should be 0.1 and 0.1 respectively:

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Start time	Duration (hours)	Data value		End time		Datetime	Output	Expected output
2	28/07/2021 09:35	1.0	0.1		28/07/2021 10:35		28/07/2021 09:00		0
3	29/07/2021 07:55	1.5	0.2		29/07/2021 09:25		28/07/2021 09:30		0.1
4	29/07/2021 11:09	0.5	0.3		29/07/2021 11:39		28/07/2021 10:00		0.1
5	29/07/2021 11:48	0.5	0.4		29/07/2021 12:18		29/07/2021 10:30		0
6	29/07/2021 11:59	1.5	0.5		29/07/2021 13:29		29/07/2021 11:00		0.3
7	30/07/2021 12:46	1.2	0.6		30/07/2021 13:58		29/07/2021 11:30		1.2
8	30/07/2021 12:53	0.8	0.7		30/07/2021 13:41		30/07/2021 12:00		0.9
9	30/07/2021 12:59	0.8	0.8		30/07/2021 13:47		30/07/2021 12:30		2.6
10	30/07/2021 13:00	0.8	0.9		30/07/2021 13:48		30/07/2021 13:00		3.5
11							30/07/2021 13:30		3
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=A2+(B2/24)