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

#### pete1229

##### New Member
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
ABCDEFGHI
1Start timeDuration (hours)Data valueEnd timeDatetimeOutputExpected output
228/07/2021 09:351.00.128/07/2021 10:3528/07/2021 09:000
329/07/2021 07:551.50.229/07/2021 09:2528/07/2021 09:300.2
429/07/2021 11:090.50.329/07/2021 11:3928/07/2021 10:000
529/07/2021 11:480.50.429/07/2021 12:1829/07/2021 10:300
629/07/2021 11:591.50.529/07/2021 13:2929/07/2021 11:000.3
730/07/2021 12:461.20.630/07/2021 13:5829/07/2021 11:301.2
830/07/2021 12:530.80.730/07/2021 13:4130/07/2021 12:000.9
930/07/2021 12:590.80.830/07/2021 13:4730/07/2021 12:302.6
1030/07/2021 13:000.80.930/07/2021 13:4830/07/2021 13:003.5
1130/07/2021 13:303
Sheet1
Cell Formulas
RangeFormula
E2:E10E2=A2+(B2/24)

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### pete1229

##### New Member
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.

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

#### pete1229

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

Book1.xlsx
ABCDEFGHI
1Start timeDuration (hours)Data valueEnd timeDatetimeOutputExpected output
228/07/2021 09:351.00.128/07/2021 10:3528/07/2021 09:000
329/07/2021 07:551.50.229/07/2021 09:2528/07/2021 09:300.1
429/07/2021 11:090.50.329/07/2021 11:3928/07/2021 10:000.1
529/07/2021 11:480.50.429/07/2021 12:1829/07/2021 10:300
629/07/2021 11:591.50.529/07/2021 13:2929/07/2021 11:000.3
730/07/2021 12:461.20.630/07/2021 13:5829/07/2021 11:301.2
830/07/2021 12:530.80.730/07/2021 13:4130/07/2021 12:000.9
930/07/2021 12:590.80.830/07/2021 13:4730/07/2021 12:302.6
1030/07/2021 13:000.80.930/07/2021 13:4830/07/2021 13:003.5
1130/07/2021 13:303
Sheet1
Cell Formulas
RangeFormula
E2:E10E2=A2+(B2/24)

Replies
3
Views
225
Replies
3
Views
212
Replies
3
Views
79
Replies
6
Views
691
Replies
1
Views
715

1,181,454
Messages
5,930,003
Members
436,716
Latest member
MiroUna

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back