# 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.

Press Enter to convert to dates.

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

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.

• 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.

• 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.

• 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.

• 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.

## Related Articles

<< Go Back to Excel Auto Formatting | Excel Cell Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF