If you type a numeric value into Excel that looks like a date, Excel will automatically convert the value to a date.
For example, if you type 1-6 or 1/6 then Excel will automatically display this value as 6-Jan because it assumes you meant for the value to be a date.
In most cases, this is a helpful correction by Excel.
However, sometimes you actually want the cell to display 1-6 or 1/6.
There are two ways to prevent Excel from changing numbers to dates:
Method 1: Specify Text as the Format
Method 2: Use an Apostrophe Before the Number
The following examples show how to use each method in practice.
Example 1: Specify Text as the Format
Suppose I attempt to enter the values 1/6, 1/16, 1/19, 2/20 … into Excel:
By default, Excel converts these values into dates.
To prevent Excel from converting these values into dates, I can first highlight the range A1:A14 where I want to enter these values, then click the Number Format dropdown menu from the Home tab, then click Text:
Now when I enter the values 1/6, 1/16, 1/19, 2/20 … into column A the values will appear as text instead of dates:
Example 2: Use an Apostrophe Before the Number
Another way of preventing Excel from changing numbers to text is to simply type an apostrophe ( ‘ ) first before entering the number.
For example, instead of typing 1/6 I would type ‘1/6 into cell A1:
I can repeat this for each value in column A:
Each value in column A now appears exactly how I entered it instead of being shown as dates.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Replace #N/A Values in Excel
How to Ignore #DIV/0! When Using Division in Excel
How to Ignore #N/A Values When Using Formulas in Excel
