How to Show the Latest Invoice Date in an Excel Pivot Table
Question details
The user needs to display the most recent invoice date in a Pivot Table, but using the Max summary function causes the date to appear as a large serial number.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Finding the latest invoice date using the Max summary function in an Excel Pivot Table.
- Observed behavior
- The Pivot Table displays dates as large numerical serial numbers rather than readable date formats.
Ensure your source data contains properly formatted dates before creating or modifying the Pivot Table.
Change Value Field Settings to Date Format
Adjust the Value Field Settings to format the calculated maximum serial number back into a readable date.
Because Excel stores dates as sequential serial numbers, summarizing a date field by 'Max' or 'Min' defaults the cell format back to General. You must explicitly tell the Pivot Table to format this maximum value as a date.
Drag your invoice date field into the Values area of the PivotTable Fields pane.
Click on the date field in the Values area, select 'Value Field Settings', and choose 'Max' under the 'Summarize value field by' tab.
In the Value Field Settings dialog box, click the 'Number Format' button located at the bottom left.
Choose 'Date' from the category list, select your preferred date style, and click 'OK' twice to apply the changes.
Easily Manage Pivot Tables with WPS Spreadsheet
WPS Office provides a powerful Spreadsheet application that fully supports Pivot Tables, including advanced date formatting and summary functions, allowing you to seamlessly analyze invoice data.
- 1. Open your data file: Launch WPS Spreadsheet and open your invoice dataset.
- 2. Insert a Pivot Table: Go to the 'Insert' tab on the top ribbon and click 'PivotTable' to create a new summary.
- 3. Set the Max Date: Drag the Invoice Date to the Values area, click its settings, select 'Max', and apply a Date number format.
Frequently Asked Questions
Why does Excel show my dates as 40000+ numbers?
Excel stores dates sequentially as serial numbers starting from January 1, 1900. When a Pivot Table summarizes these dates (like using Max or Min), it often defaults to a General number format, revealing the underlying serial number instead of the formatted date.
Can I show both the earliest and latest invoice dates in the same Pivot Table?
Yes. You can drag the invoice date field into the Values area twice. Set the first one to summarize by 'Min' (for the earliest date) and the second to 'Max' (for the latest date), then apply the Date number format to both via Value Field Settings.
Will refreshing the Pivot Table lose my custom date format?
No, if you apply the formatting through the 'Value Field Settings' > 'Number Format' dialog, the date format will remain intact even after you refresh the Pivot Table data or add new rows to your source data.
