Formatting Issues

How to Fix Excel Date Format Showing Wrong Month and Year

Partner Editor Sep 29, 2026 870 views

Question details

The user is encountering an issue where custom-formatted dates display an incorrect month and year, such as showing Aug-25 when the intended year was 2022.

Fix Excel Date Format Showing the Wrong Month and Year

Product: Excel
Device & OS: not provided
Scenario: Applying the custom mmm-yy date format to cells or typing abbreviated date text directly into the worksheet.
Observed behavior: Excel incorrectly interprets the text input as a day in a different year (e.g., assuming Aug-22 means August 22, 2025) rather than the intended month and year combination.

Before you start

Select the problematic cell and check the Formula Bar to see the complete underlying date Excel has stored, as it may differ drastically from the formatted text displayed on your sheet.

Solution 1Recommended

Enter Complete Dates and Apply Custom Formatting

Input the full date manually to ensure Excel registers the correct year in its system before applying formatting to hide the day.

Excel relies on the underlying serial date stored in its system. By entering a complete date string, you remove any ambiguity that causes Excel to misinterpret the year as a day.

Input the full date

Select an empty cell, type the complete intended date (such as 8/1/2022), and press Enter.

Open Format Cells

Right-click the cell containing the new date and select 'Format Cells' from the context menu, or press Ctrl+1 on your keyboard.

Apply custom format

Navigate to the 'Number' tab, select 'Custom' from the category list, type mmm-yy into the Type box, and click OK.

Solution 2

Convert Text Strings into Real Dates Using DATEVALUE

Use the DATEVALUE formula to convert incorrectly interpreted text dates back into accurate Excel serial dates.

Format Dates Easily with WPS Spreadsheet

Easily Format and Convert Dates with WPS Spreadsheet

WPS Spreadsheet provides intuitive and precise formatting tools to handle custom dates perfectly, ensuring your months and years are accurately displayed without hidden data errors.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your dates.
2. Select the date cells: Highlight the cells or the entire column that needs formatting.
3. Open Format Cells: Press Ctrl+1 to quickly open the Format Cells dialog box.
4. Apply custom formatting: Go to the Custom category, type mmm-yy in the input box, and click OK to correctly format the values.

100% compatible with Microsoft Excel date formats, shortcuts, and formulas.Easily apply custom formatting like mmm-yy with a familiar, clean interface.Free, lightweight, and fast for processing large numerical datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change 'Aug-22' to August 22 of the current year?

When you type 'Aug-22' without explicitly stating a full year, Excel evaluates the string and interprets the '22' as the day of the month. It then automatically assigns the current system year to complete the date.

How can I reveal the true underlying date of a formatted cell?

Click on the cell and look at the Formula Bar at the very top of the spreadsheet workspace. It will display the complete, unformatted date and time that Excel actually stores, regardless of the mmm-yy format applied to the cell visually.

Can I apply the mmm-yy format to an entire column at once?

Yes. Click the letter at the top of the column to select it entirely, press Ctrl+1 to open the Format Cells dialog, and apply the mmm-yy custom format. This will standardize the display across all current and future date entries in that column.

Document & File Issues