Converting half hourly data to hourly data

Hi all

I have some data recorded at 30-minute intervals and I would like to convert to hourly values. The raw data is contained in columns A, B and C in the attachment. The output should take the form indicated in columns G, H and I where column H contains hourly total Radiation and column I contains hourly average Temperature. The dates in column G have been created manually but I need a way of doing it using the dates in column A. How do I use excel to get such an output?

Regards

Joe

You can do this in a Pivot Table as well.

Drag TIMESTAMP into ROWS, Radiation into VALUES, Temperature into VALUES (change from Sum to Average) then group TIMESTAMP by Days and Hours.

Thanks 63falcondude, both approaches work perfect.

Thanks maras_mak. However it worked for columns G and H. For column I, the result is 18.83667523 in column I2 which seems to be incorrect if you look at the first few raw data entries. In addition, when I drag the formula down, all cells are populated with an error massage #VALUE!
but thanks for the effort and for your time.

Thanks maras_mak. However it worked for columns G and H. For column I, the result is 18.83667523 in column I2 which seems to be incorrect if you look at the first few raw data entries. In addition, when I drag the formula down, all cells are populated with an error massage #VALUE!
But thanks for the effort and for your time.

Happy to help. Thanks for the rep!

Thanks FerdyHar

Originally Posted by 63falcondude

Try these:

G2 =MIN(A:A)+(ROWS($1:1)-1)/24

H2 =SUMIFS(B:B,A:A,">="&G2,A:A,"<="&G2+TIME(0,30,0))

I2=AVERAGEIFS(C:C,A:A,">="&G2,A:A,"<="&G2+TIME(0,30,0))

Hi 63falcondude, when I am doing what you have shown for column G2, mine is showing 0101/1900 for dates. Times are getting consolidated into hourly format though. Please have a look at the column K in the attached sheet and could you suggest a solution please?


Many Thanks