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.
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) |