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

pete1229

New Member
Joined
Nov 6, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 6, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 6, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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)
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top