Formatting Issues

How to Prevent Excel from Changing Two-Digit Years to Four-Digit Years

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs a way to stop Excel from automatically misinterpreting two-digit year entries (such as interpreting "31" as "1931" instead of "2031") when workbooks are reopened.

Product: Excel
Device & OS: not provided
Scenario: Entering dates using only a two-digit year format and saving the file, particularly when reopening workbooks or exporting to CSV formats.
Observed behavior: Excel automatically assigns the wrong century to ambiguous two-digit years (e.g., converting 9/1/31 to September 1, 1931), which permanently alters the underlying date value.

Before you start

Keep in mind that Excel relies on your computer's operating system regional settings to determine the century cutoff for two-digit years. By default in Windows, years 00 through 29 are treated as the 2000s, while 30 through 99 are treated as the 1900s.

Solution 1Recommended

Use Four-Digit Years with Custom Cell Formatting

The most reliable way to prevent date ambiguity is to enter the full four-digit year and apply a custom format if you prefer to visually display only two digits.

Entering four digits guarantees that Excel stores the exact century you intend. By using custom cell formatting, you can still maintain the concise look of a two-digit year on your spreadsheet without risking data corruption when the file is saved or reopened.

Select the target cells

Highlight the specific cells, columns, or rows where you plan to enter your dates.

Open the Format Cells dialog

Right-click the selected area and choose 'Format Cells' from the context menu, or simply press the Ctrl+1 keyboard shortcut.

Apply a custom date format

Navigate to the 'Number' tab, select 'Custom' from the Category list, and type 'm/d/yy' or 'mm/dd/yy' into the Type input box.

Input the unambiguous date

Click 'OK'. Now, enter your date using the full four-digit year (e.g., 9/1/2031). Excel will safely store the year as 2031 but display it as 9/1/31.

Warning for CSV Exports: When saving your spreadsheet as a CSV file, verify that your dates are displaying correctly first. CSV files store the exact plain text displayed in the cell, not the underlying Excel date formatting.

Manage Dates Effectively

Format and Manage Dates Seamlessly with WPS Spreadsheet

Avoid frustrating date formatting issues using WPS Spreadsheet. It offers highly compatible, intuitive cell formatting tools that let you control exactly how your dates are stored and displayed without automatic conversion errors.

1. Open your file: Launch WPS Spreadsheet and open the workbook containing your date entries.
2. Access cell formatting: Select the cells you want to modify and press Ctrl+1 to open the Format Cells window.
3. Set custom formatting: Under the Number tab, click on Custom, enter 'mm/dd/yy' to display two-digit years, and click OK.
4. Enter four-digit dates safely: Type your dates using four digits (e.g., 2031). WPS Spreadsheet will store the correct century while displaying only the last two digits.

Easily apply custom date formats to separate stored values from visual displayFully compatible with Microsoft Excel date formats and CSV exportsIntuitive Format Cells dialog for quick adjustmentsLightweight and fast for handling large datasets with complex date rules

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel change my year '31' to '1931' instead of '2031'?

Excel interprets two-digit years based on the 2029 rule set by the Windows operating system. Two-digit years from 00 to 29 are interpreted as 2000 to 2029, while years from 30 to 99 are interpreted as 1930 to 1999. Entering '31' triggers the 1900s rule.

Can I change the default two-digit year cutoff in Excel?

Yes, but this is done through Windows, not Excel. Go to the Windows Control Panel > Region > Additional settings > Date tab. Look for the setting 'When a two-digit year is entered, interpret it as a year between' and adjust the range according to your needs.

How do I fix CSV dates that lost their century information?

Because CSV files save the displayed text, if a two-digit year was displayed upon saving, the century information is permanently lost in the CSV. You must open the original Excel workbook (.xlsx), change the date format to display four-digit years (like mm/dd/yyyy), and then re-save the file as a CSV.

Document & File Issues