Excel - Sum consecutive data every 30 minutes interval

Question

I have data collected from a rain gauge in the following format:

Index Data Timestamp

1. 0    2020-01-05T00:00:00
2. 0    2020-01-05T01:00:00
3. 1    2020-01-05T01:20:15
4. 0    2020-01-05T02:00:00
5. 1    2020-01-05T02:09:00
6. 1    2020-01-05T02:09:45
7. 1    2020-01-05T02:20:00
8. 1    2020-01-05T02:20:01
9. 0    2020-01-05T03:00:00

I would like to sum the consecutive data every 30 minutes. Something like the following:

Index Data Timestamp

1. 0    2020-01-05T00:00:00
2. 0    2020-01-05T00:30:00
3. 0    2020-01-05T01:00:00
4. 1    2020-01-05T01:30:00
5. 0    2020-01-05T02:00:00
6. 4    2020-01-05T02:30:00
7. 0    2020-01-05T03:00:00

I have already obtained just the 24h time from the timestamp and tried to workout whether subtracting 2 time stamps is less than 30 minute as per the following formula:

=IF(((time)-(time-1))<=0.020833,"ok","not within 30min interval")

The Excel formula I had in mind was to obtain something along the line of a SUMIFS function which would sum all the consecutive values within a 30min interval

I have tried to put in place some sort of COUNTIF to check for consecutive data within the 30min interval and have tried to work out a SUMIFS and a SUMPRODUCT function, but I am struggling to put things together and with the logic.

Thank you

Answer 1

You can use this formula:

=DATEVALUE(SUBSTITUTE(C2,"T"," "))+CEILING.MATH(ROUND(TIMEVALUE(SUBSTITUTE(C2,"T"," ")),5),"0:30")

Broken down into steps:

1. To get Excel to recognize the text as a date/time, remove the T:

   =SUBSTITUTE(C2,"T"," ")

2. To get the date from that date/time:

   =DATEVALUE(D2)

3. To get the time from that date/time:

   =ROUND(TIMEVALUE(D2),5)

(I'll explain why the ROUND is needed here a bit further down)

4. To calculate the next 30-minute increment:

   =CEILING.MATH(ROUND(TIMEVALUE(SUBSTITUTE(C2,"T"," ")),5),"0:30")

Then concatenating the date with the 30-minute increment gives you the formula at the top of the answer. You can then pivot the table to sum the value by the increment.

Note on the call to ROUND in step 3:

When I first wrote the formula, I didn't use ROUND in step 3, but I found that the increment coming out for 2:00:00 was 2:30:00, which wasn't right. On inspection, I found that the TIMEVALUE function wasn't returning the correct level of accuracy. Rounding to 5 decimal places fixed that.

EDIT:

Since you have Excel 365, I'm assuming you also have access to the SEQUENCE function.

To aggregate without a pivot table, you can do the following (assuming you don't have a pre-existing list of increments):

1. Calculate the number of 30-minute increments between the MIN TimestampGroup and the MAX timestamp group
2. Create a sequence containing that many increments (so that increments not in the source data are also represented)
3. Use SUMIF to refer back to the source data and sum the Data by TimestampGroup
4. Optionally create a GroupIndex for the aggregated table

The formulas in the aggregated table are:

GroupIndex:

=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1)

This just tells you how many 30 minute increments there are between the smallest TimestampGroup and the largest TimestampGroup and returns an integer sequence for that many increments.

TimestampGroup:

=SEQUENCE((MAX($D$3:$D$11)-MIN($D$3:$D$11))*48+1,1,MIN($D$3:$D$11),1/48)

Again, using the sequence function, return a list of TimestampGroups, but instead of starting the sequence at 1 and incrementing by 1, which are the default 3rd and 4th parameters, start the sequence at the min TimestampGroup and increment by 30 minutes, which is 1/48 of a day.

Value:

=SUMIF($D$3:$D$11,$F16,$B$3:$B$11)

Sum the Data column for the rows indicated by the TimestampGroup on the current row of the aggregated table.