How to Stop Excel from Changing Numbers to Dates (5 Ways)

Method 1 – Utilizing Format Cells Feature to Prevent Excel from Converting Numbers to Dates

The format cells feature allows us to change the appearance of cell numbers without changing the original number. We know that the fraction number of 0.2307 is 3/13. So, we enter the number into the selected cell.

Utilize Format Cells Feature to Prevent Excel from Converting Numbers to Dates

Press Enter to convert to dates.

This will happen for every cell while entering fraction numbers or numbers with ‘/’ or ‘–’.

Utilize Format Cells Feature to Prevent Excel from Converting Numbers to Dates

To stop Excel from converting numbers to dates we are going to use text formatting.

STEPS:

Select the cells to enter the fraction numbers.
Go to the Home tab from the ribbon.
Click on the tiny icon in the Number group to open the Format Cells dialog box.
Or use the keyboard shortcut Ctrl + 1 to display the Format Cells window.

From the Format Cells dialog box, go to the Number menu and select Text.
Click OK.

Utilize Format Cells Feature to Prevent Excel from Converting Numbers to Dates

Entering any fraction number into the selected cells stops the automatic change from numbers to dates.

Note: Before entering the number, we must alter the format. If we perform this after entering the number, the format will change to the text, but we will only receive the date’s numeric value rather than the precise number or text string.

Method 2 – Halting Conversion of Numbers to Dates Using Apostrophe in Excel

STEPS:

Select the cell to enter the fraction number.
Add an apostrophe(‘) before entering the number. This won’t show in the cell but if you look at the formula bar, the apostrophe can be seen.
Press Enter.

Halt Conversion of Numbers to Dates Using Apostrophe in Excel

Do this for all the range of cells. Adding an apostrophe will prevent Excel from changing the format.

Method 3 – Adding Space to Stop Excel from Changing Numbers to Dates

STEPS:

Select the cell to enter the fraction number.
Keep a space before the number.
Press Enter.

Do this for every cell in the range. By adding a space, Excel won’t change the format.

Method 4 – Stopping Automatic Change from Numbers to Dates by Inserting Zero & Space

STEPS:

Select the cell to enter the fractional number.
Input a 0 and a space before the number.
Press Enter.

Stop Automatic Change from Numbers to Dates by Inserting Zero & Space

The zero leaves the cell and the cell changes to the fraction number type.
If you check the formula bar this will show the decimal number of the fraction.

You won’t be able to use this for every fraction. For example, 0.66667 is a fraction of 8/12 but while using the zero and space together, this shows 2/3 as these numbers are divisible.

Method 5 – Applying Excel VBA to Prevent Automatic Conversion

STEPS:

Go to the Developer tab from the ribbon.
Click on Visual Basic from the Code category to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.

Apply Excel VBA to Prevent Automatic Conversion

Click on Module from the Insert drop-down menu bar.

This will appear in the Visual Basic Editor where we write our code.
Then, click on Module from the Insert drop-down menu bar.

Enter the following the VBA code.

VBA Code:

Sub Stop_Change()
Dim d As Date
d = Date
With Range("D5:D10")
    .NumberFormat = "@"
    .Value = Format(d, " ")
End With
End Sub

Run the code by clicking on the RubSub button or pressing F5.

Note: You need to change the range as per your requirements.

If you enter any number with ‘/’ or ‘–’, it will not change.

Things to Keep in Mind

While using Excel VBA code in your worksheet, make sure you save the file with Excel Macro-Enabled Workbook and the extension will be .xlsm.

Download Practice Workbook

Stop Changing Numbers to Dates.xlsm