Calculation Issues

How to Fix Excel Changing Entered Dates to 09/01/1900

Guest Writer Oct 10, 2026 871 views

Question details

Users enter a short date (such as 01/12) into a cell, and Excel incorrectly converts and displays it as 09/01/1900, even when the cell is specifically formatted as a date.

How to Fix Excel Changing Entered Dates to 09/01/1900

Product: Microsoft Excel
Device & OS: macOS / Windows
Scenario: Entering dates manually into a spreadsheet for tracking or calculation purposes.
Observed behavior: Excel overrides the user's manual date input and automatically changes the cell value to 09/01/1900, ignoring standard date formatting rules.

Before you start

Before modifying your system or application settings, test if this behavior happens in a brand-new blank workbook. Date systems and transition formula evaluations are often saved on a per-workbook basis.

Solution 1Recommended

Disable Transition Formula Evaluation and Check Date Systems

Turning off legacy Lotus compatibility settings and ensuring the correct date system is used will prevent Excel from miscalculating date inputs.

Excel has a feature called 'Transition formula evaluation' designed for compatibility with older spreadsheet programs like Lotus 1-2-3. When enabled, it evaluates text and date inputs differently, which frequently causes short dates (like 01/12) to be interpreted as mathematical division and converted into the 1900 date format.

Open Excel Preferences or Options

On Windows, click File > Options. On a Mac, click Excel in the top menu bar and select Preferences.

Navigate to the Compatibility Settings

Scroll down to the 'Compatibility' or 'Advanced' section depending on your version of Excel.

Turn off Transition Formula Evaluation

Locate the setting named 'Transition formula evaluation' and ensure the checkbox next to it is unchecked.

Verify Date System

In the 'Advanced' section under 'When calculating this workbook', check whether 'Use 1904 date system' is toggled. If you don't specifically need it for Mac-legacy files, keep it unchecked to use the standard 1900 date system.

Disable Transition Formula Evaluation and Check Date Systems

Settings Applied: Once unchecked, re-enter your date as '01/12' and it should display correctly for the current year.

Solution 2

Verify macOS System Language and Region Date Formats

If you are on a Mac, Excel relies on your operating system's regional settings to interpret date separators. Mismatched settings cause parsing errors.

Solution 3

Enable System Separators in Excel

Ensuring Excel uses your computer's default system separators helps properly parse slashes or dashes used in dates.

Manage Dates Seamlessly with WPS Spreadsheets

If you are tired of battling legacy calculation settings and date system errors, try using WPS Spreadsheets. It intelligently recognizes standard date inputs, formats them accurately without relying on outdated compatibility quirks, and works flawlessly with your existing Excel files.

1. Open Your File in WPS: Launch WPS Office and open your existing spreadsheet.
2. Select Target Cells: Highlight the cells or columns where you need to input your dates.
3. Format as Date: Right-click the selection, choose 'Format Cells', navigate to the 'Number' tab, and select 'Date'.
4. Enter Your Dates: Type your short date (e.g., 01/12). WPS Spreadsheets will accurately register the date for the current year without reverting to 1900.

Intelligent cell formatting automatically parses standard date inputs correctly100% format compatibility with Microsoft Excel (.xlsx, .xls) filesFree and lightweight alternative to heavy spreadsheet applicationsFamiliar tabbed interface requiring zero learning curve

Frequently Asked Questions

Why does Excel use the year 1900 as a default date?

Excel stores dates as sequential serial numbers starting from January 1, 1900. If a cell contains a value that Excel computes as zero or interprets through legacy formula transition settings, it defaults to the starting point of its calendar, which is 01/01/1900 (or 09/01/1900 depending on division interpretation).

What is the difference between the 1900 and 1904 date systems?

The 1900 date system sets the starting serial number date to January 1, 1900. The 1904 date system, historically used by early Macintosh computers to handle leap years differently, starts on January 1, 1904. Copying dates between workbooks using different systems can cause dates to incorrectly shift by four years and one day.

How can I stop Excel from automatically changing my fractions to dates?

If you want to enter a fraction or a text string like '1/12' without Excel turning it into January 12th, simply type an apostrophe (') before the number (e.g., '1/12). Alternatively, highlight the cells and format them as 'Text' before typing your data.

Excel & Data Problems