Excel
Stop Automatic Date Conversions

Why Does Excel Convert Numbers to Dates?

Excel's smart features sometimes lead to frustration. One common issue is the automatic conversion of numbers into dates. Entering '1/2' can result in '01-02-2020'.

Excel does this to make data entry easier, assuming you're working with dates. However, if you intend to use the number, this can be problematic, changing the underlying value instead of preserving your original input.

Methods How to Stop Excel from Changing Numbers to Dates

The key to preventing date conversions is to tell Excel that your input isn't a date. Here's how:

Method 1: Change the Cell Format to Text: This is the simplest and most effective approach for ranges of cells. Select your cells, go to the 'Home' tab, and in the 'Number' group, choose 'Text' from the dropdown. Now, Excel will treat your entries as text, preserving your formatting. Ensure you apply the format *before* entering the number.

Method 2: The Apostrophe Trick: For individual cells, use this quick method. Simply add an apostrophe (') before the number. Excel recognizes this as text. This is perfect for a few isolated entries and won't affect formulas that use the value.

“

The easiest way to stop Excel date conversions is by formatting the cell as text *before* you enter the number.

Excel Expert

Convert ing Existing Dates to Text

If you have existing dates that you need to display as text, the apostrophe method works well here, too! Simply add the apostrophe at the beginning of the cell's content to instantly convert the date to text.

You may occasionally see a green triangle in the top-left of a cell. This is Excel's way of alerting you that a number is stored as text. Since this is the desired result, you can ignore these indicators or dismiss them.