Summarising Data in Excel

Hi

I am working with huge amounts of climate data. I have rainfall data recorded half hourly from 1 August 2017 to 9 October 2017 and I used a pivot table to obtain daily values (see attachment). The daily totals are accumulated from 12 A.M. of one day to 12 A.M. of the following day which is fine. I would like to obtain daily totals accumulated from any other time interval which defines a 24-hour period other than 12 Am -12 AM e.g. 5 A.M. to 5 A.M. of the following
day. I further want to obtain daily totals accumulated during day time only e.g. from 6 A.M. to 6 P.M. of the same day. My question is: how can I use excel to do that?

Joseph

You could do it with formulas. See attached.
Change the times in the yellow cells to dictate at what time a 24 hour period should start/end.
Change the times in the green cells to specify a start and end time.

Hope that helps.

BSB

Attached is your file with a macro,
to enter the hour and it will build blocks.
These can be put in pivot table now.

Your template works BadlySpelledBuoy, fantastic! Thank you so much. Suppose column B (Raintotal) was Average Temperature and I wanted to obtain daily averages in a similar way, can I replace SUMIFS with AVERAGEIFS in the logical functions?

Thanks Ranman, will try the macro approach.

Originally Posted by jmasanganise

Your template works BadlySpelledBuoy, fantastic! Thank you so much. Suppose column B (Raintotal) was Average Temperature and I wanted to obtain daily averages in a similar way, can I replace SUMIFS with AVERAGEIFS in the logical functions?

Happy to help.

As for calculating avg. temps, changing SUMIFS for AVERAGEIFS would work indeed.

BSB