Formatting Issues

How to Keep January 2026 from Displaying as JAN-26 in Excel

Nimra MalikNimra Malik Nov 10, 2026 868 views

Question details

The user wants to prevent Excel from automatically reformatting the typed text 'January 2026' into the abbreviated date format 'JAN-26'.

How to Keep January 2026 from Displaying as JAN-26 in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Entering a full month and year combination into a spreadsheet cell for reporting or tracking purposes.
Observed behavior
Excel automatically interprets the typed month and year as a date and applies its default short date format, displaying it as 'JAN-26' instead of the full 'January 2026'.
Before you start

Before applying a fix, decide whether you need this cell to function as a calculable date for sorting and formulas, or if you simply need it to act as a plain text label.

Solution 1Recommended

Use a Custom Date Format (Recommended)

This method forces Excel to display the full month and year while keeping the underlying value as a real date, which is essential if you plan to sort the data chronologically or use it in formulas.

By entering a valid date and applying a custom format code, you can override Excel's default short date abbreviation without losing the mathematical properties of the date.

1
Enter a valid standard date

Select your target cell and type a full date representation for that month, such as '1/1/2026', and press Enter.

2
Open the Format Cells dialog

Right-click the cell and select 'Format Cells' from the context menu, or press the 'Ctrl + 1' keyboard shortcut.

3
Apply the custom format code

In the Format Cells window, go to the 'Number' tab and select 'Custom' from the Category list. In the 'Type' input box, clear the existing text and enter 'mmmm yyyy'.

4
Save the changes

Click 'OK'. The cell will now visually display 'January 2026' exactly as you want, while safely storing the actual date value in the background.

Use a Custom Date Format (Recommended)
Sorting Compatibility: Because the underlying value is still a recognized date (e.g., January 1, 2026), any chronological sorting or date-based filtering will continue to function flawlessly.
Seamless Cell Formatting

Format Dates Exactly How You Want in WPS Spreadsheet

WPS Spreadsheet offers powerful, intuitive formatting tools that seamlessly handle custom dates and text conversions. You can easily apply exact date formats without struggling with unexpected auto-corrections.

  1. 1. Open your document in WPS Spreadsheet: Launch WPS Office and open your spreadsheet file.
  2. 2. Access cell formatting: Select the cells you want to modify, right-click, and choose 'Format Cells', or use the 'Ctrl + 1' shortcut.
  3. 3. Apply Custom formatting: Navigate to the 'Number' tab, select 'Custom', and type 'mmmm yyyy' in the Type box.
  4. 4. Confirm and view: Click 'OK'. Any date entered as 1/1/2026 will instantly display beautifully as January 2026.
100% compatible with Microsoft Excel custom date formatsIntuitive Format Cells interface for easy data managementLightweight, fast, and completely free to useAvailable across Windows, Mac, Linux, iOS, and Android
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically change my date to JAN-26?

Excel has a built-in feature called 'Auto Format' that detects when you type something resembling a date. By default, it applies your system's short date configuration to save column space and standardize date entries.

Can I turn off Excel's automatic date formatting permanently?

Excel does not offer a master switch to completely disable automatic date recognition. The best workaround is to pre-format your blank columns as 'Text' before entering your data.

Will formatting 'January 2026' as text affect how I sort my data?

Yes. If the cell is formatted as text, Excel sorts the data alphabetically (A to Z). This means 'April 2026' would be sorted before 'January 2026'. If you need chronological sorting, you must use the 'Custom Date Format' solution.

How do I apply this custom date format to an entire column at once?

Click the column letter (e.g., 'A') at the top of the worksheet to highlight the entire column. Then right-click, select 'Format Cells', choose 'Custom', and apply the 'mmmm yyyy' format code.