Best Way to Store Month and Year in Excel for Sorting
Question details
The user wants to know the best method for entering and storing month and year data in Excel so that PivotTables and charts can accurately sort and group the information chronologically.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Preparing and structuring date data for accurate chronological analysis in PivotTables and charts.
- Observed behavior
- Text-based month and year entries fail to sort chronologically, requiring a proper date storage method to maintain the underlying numerical date value.
Ensure your data column is wide enough to display dates and clear any existing text formatting applied to the cells before entering your dates.
Use a Valid Date and Apply Custom Month-Year Formatting
Storing the entry as a full, valid date and changing its display format ensures Excel recognizes it numerically for chronological sorting and PivotTable grouping.
Excel handles dates as sequential serial numbers. If you type a month and year as plain text (like 'Jan-2026'), Excel treats it as text, causing it to sort alphabetically (e.g., April before January). To fix this, you should always enter a valid date and use Custom Formatting to change how it looks on the screen.
Highlight the cells or the entire column where you want to enter the month and year data.
Type a complete date, such as 1/1/2026 or 01/01/2026, even if your ultimate goal is to only display the month and year.
Right-click the selected cells and choose 'Format Cells' from the context menu, or press the keyboard shortcut Ctrl+1.
Navigate to the 'Number' tab and select 'Custom' from the Category list on the left side.
In the 'Type' input box, enter 'mmm-yyyy' (to display as Jan-2026) or 'yyyy-mm' (to display as 2026-01), then click 'OK'.

Format Dates Seamlessly in WPS Spreadsheet
WPS Spreadsheet offers robust date formatting and PivotTable features identical to Excel, allowing you to easily store, group, and analyze your time-series data without formatting headaches.
- 1. Open your workbook: Launch WPS Spreadsheet and open your document, then select your date column.
- 2. Input the date: Type a complete, valid date (e.g., 1/1/2026) into the cell.
- 3. Access cell formatting: Press Ctrl+1 to quickly open the Format Cells dialog.
- 4. Set custom display: Select 'Custom' under the Number tab and input your desired month-year format like 'mmm-yyyy'.

Frequently Asked Questions
Why does my PivotTable group dates alphabetically instead of by month?
This happens if your dates were entered or stored as text strings (e.g., typing 'Jan-2026' into a text-formatted cell). You must convert them to valid Excel serial dates and use custom cell formatting to display them chronologically.
How do I extract just the month and year from a full date into a new column?
You can use the TEXT function to extract it. For example, enter `=TEXT(A2, "mmm-yyyy")` to display the month and year. Keep in mind that this converts the result to text, so for PivotTable chronological sorting, it is usually better to keep the underlying date value and format the original cell.
Can PivotTables automatically group my full dates into months and years?
Yes. If your column contains valid full dates, simply create a PivotTable, right-click any date in the Row Labels, select 'Group', and then highlight 'Months' and 'Years' to automatically categorize your data.




