Pivot Table Issues

Fix Pivot Table Dates Changing to Serial Numbers After Refresh in Excel

Camila Milosovich Sep 28, 2026 872 views

Question details

The user needs to maintain correct date formatting in a pivot table, as dates grouped by year and month revert to serial numbers upon refreshing.

How to Fix Pivot Table Dates Changing to Serial Numbers After Refresh

Product: Excel
Device & OS: not provided
Scenario: Refreshing an Excel pivot table that is linked to an external source workbook.
Observed behavior: Dates change to serial numbers after a refresh. The refresh only succeeds when the external source workbook is open, and modifying the source date format does not fix the pivot table display.

Before you start

Verify that your source workbook contains valid, uncorrupted date entries and take note of the exact file path to the external source file.

Solution 1Recommended

Use Power Query to Import Data and Format the Pivot Field

This is the most reliable method for updating pivot tables from closed external workbooks while permanently retaining proper date formatting.

Excel stores dates as numerical serial numbers. When a standard pivot cache attempts to pull data from a closed workbook, it often loses custom formatting and reverts to these raw numbers. Power Query establishes a robust data connection that reliably retrieves and structures information regardless of whether the source file is open.

Import the source data

Navigate to the Data tab, click on 'Get Data', select 'From File', and then 'From Workbook'. Locate and select your external source file.

Load to a PivotTable

In the Navigator window, select your data table. Click the arrow next to 'Load' or 'Close & Load', choose 'Close & Load To...', and select 'PivotTable Report'.

Format the pivot field

Right-click the date field in your newly generated Pivot Table, select 'Value Field Settings' or 'Number Format', choose 'Date', and apply your desired Year/Month format.

Formatting Retained: Applying the number format directly within the Pivot Table settings (rather than the source sheet) ensures the formatting persists across all future refreshes.

Solution 2

Apply Date Formatting Directly to the Pivot Field

If you prefer not to use Power Query and are willing to keep the source workbook open during refreshes, apply the format to the pivot field settings directly.

Easily Manage and Format Pivot Tables with WPS Spreadsheet

WPS Spreadsheet provides a seamless and highly compatible environment for analyzing data. You can easily create, refresh, and format Pivot Tables without losing your custom date groupings.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your raw data.
2. Insert a PivotTable: Navigate to the Insert tab on the top ribbon and click 'PivotTable' to create a new summary table.
3. Group your dates: Drag your Date field into the Rows area, right-click any date cell in the table, and select 'Group' to organize them by Year and Month.
4. Lock the number format: Right-click the grouped field, choose 'Field Settings', click 'Number Format', and set it to Date to ensure the formatting survives future refreshes.

Fully compatible with Microsoft Excel (.xlsx) file formats and pivot caches.Intuitive user interface for grouping dates by months, quarters, and years.Reliable data refresh capabilities that maintain your applied formatting.Lightweight software that processes large datasets quickly and efficiently.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change my dates to 5-digit numbers like 44000?

Excel stores all dates as sequential serial numbers for calculation purposes, starting from January 1, 1900. When formatting is stripped away during a pivot table refresh from a closed file, Excel defaults back to displaying this underlying raw serial number.

Does changing the date format in my source data fix the pivot table?

No. Changing the display format in the raw source data generally does not carry over to an existing Pivot Table's layout. You must apply the number formatting directly within the Pivot Table's 'Value Field Settings'.

Why won't my pivot table refresh when the source file is closed?

A standard pivot cache sometimes fails to read correctly from closed external workbooks due to link restrictions. Using Power Query to import the data establishes a stable connection that can bypass this limitation.

Excel & Data Problems