Formula to automatically display highs and lows for each day of the month

Hi all,

I've recently purchased a weather station, which I download the data from each morning. At present I look through to find the high and low temperatures etc for each day. What I would like is for the spreadsheet to look through each month's raw data and tell me the highs and lows/ totals for each individual day and what time they occurred.

Maximum and minimum temperatures are recorded over the period 0900 to 0900 each day, as is rainfall. Rainfall is the total amount to have fallen in this period. All other extremes are calculated over the calendar day. Wind speed is the average from midnight to midnight.

I've attached a sample sheet. I don't have a clue where to start with this so the sheet is purely a very basic layout of what I imagine my sheet might look like. Ideally, I'd like to have all of the "raw" data in one file and my data for the month in a separate file - I'm not sure if this complicates things too much?

Thanks in advance for any help.

Luke

Hi

I suppose you need a pivot table

I adapt your labels to create a pivot table and a new scale of time 9:9 using

Date_9 =([@Date]-9/24+[@Time])

Other columns are added as Day, Month, Year of a regular date and Day_9, Month_9 and Year_9 to meet your needs.

See the Pivot Table in sheet Folha1.

Thanks for your reply Jose.

The pivot table you added shows the maximum and minimum temperatures and total rainfall for the day running from midnight to midnight. Is there a way I can get it to show highs, lows and totals for the period 0900 to 0900?

Also, I should have put in my original post that the minimum temperature is recorded for the 24 hours up to 0900 on the date (i.e. its assumed it occurred overnight). The maximum is recorded up to 0900 but then "thrown back" to the previous day (i.e. it's assumed it occurred during the daytime of the previous day).

Thanks,

Luke

Use in Pivot Table

Year_9
Month_9
and Day_9

See the file