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