Group Daily Temperatures by Month in an Excel PivotTable
Question details
Calculate monthly temperature extremes by year from daily records whose dates are stored as YYYYMMDD text.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains daily dates from 1833 through 2018 and corresponding maximum and minimum temperatures.
- Observed behavior
- The desired result is a table showing the highest daily maximum and lowest daily minimum for each year and month, including years before 1900.
Check that each date is an eight-character YYYYMMDD value and that the temperature columns contain numbers. The steps below assume dates are in column A, daily maximums in B, and daily minimums in C, with headers in row 1.
Summarize temperatures with helper columns and a PivotTable
Use numeric year and month fields to group records without converting pre-1900 dates into Excel dates.
Enter Year in D1. In D2, enter =VALUE(LEFT(A2,4)), then fill the formula down through the last data row.
Enter Month in E1. In E2, enter =VALUE(MID(A2,5,2)), then fill the formula down. The resulting numbers 1 through 12 keep the months in calendar order.
Select the source range, including all five headers, then choose Insert > PivotTable. Select where to place the PivotTable and confirm.
In the PivotTable Fields pane, drag Year and then Month to Rows. Drag the daily maximum and daily minimum fields to Values.
Open Value Field Settings for the daily maximum field and choose Max. Open the same settings for the daily minimum field and choose Min. Each row now shows the highest daily maximum and lowest daily minimum for that year and month.

Group monthly temperature extremes in WPS Spreadsheets
Use year and month helper columns and a PivotTable in WPS Spreadsheets to summarize daily temperature extremes, including records from before 1900.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select the workbook containing the daily dates and temperatures.
- 2. Add grouping columns: With dates in column A, enter Year and Month as new headers. Use =VALUE(LEFT(A2,4)) for the year and =VALUE(MID(A2,5,2)) for the month, then fill both formulas down.
- 3. Insert a PivotTable: Select the data and helper columns, then choose Insert > PivotTable and select a location for the report.
- 4. Set up the summary: Place Year and Month in the row fields and add the daily maximum and minimum temperature fields to Values. Set their value calculations to Max and Min, respectively.

Frequently Asked Questions
Why not group the original dates directly in the PivotTable?
The YYYYMMDD values are text, and the dataset includes dates before 1900. Extracting numeric year and month fields avoids relying on Excel to recognize those historical values as dates.
How do I show monthly averages instead of extremes?
In the PivotTable, open Value Field Settings for the relevant temperature field and select Average. This calculates the average of the daily values in each year and month group.
How do I update the PivotTable after adding records?
Make sure the PivotTable source includes the new rows, then right-click the PivotTable and select Refresh. If needed, use PivotTable Analyze > Change Data Source to expand the range first.
Can VBA calculate the same monthly results?
Yes. A VBA macro can read the source range into an array, group records by the year and month extracted from each date, calculate the extremes, and write the results to a worksheet in one operation.




