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

The features of Microsoft Excel make entering dates simpler. For instance, 3/13 becomes 13 Mar. This is quite annoying when we type that which we wouldn’t want to convert to date. Sadly, there is no way to stop this from happening. However, there are workarounds available. In this article, we will demonstrate 5 different ways to stop Excel from changing numbers to dates.

The only method for preventing Excel from converting these numbers into dates is by explicitly telling it that they are not numbers. For instance, suppose we have a dataset containing six numbers, now we want to find the fraction of those numbers. But when we type any fraction number it’s automatically converted to dates. So, let’s look at the methods to stop this.

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.

And then press the Enter key and it’s automatically converted to dates (see the screenshot below).

This will happen for each and 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. For this, we need to go along with the steps below.

STEPS:

Firstly, select the cells where you want to enter the fraction numbers.
Secondly, go to the Home tab from the ribbon.
Thirdly, click on the tiny icon in the Number group to open the Format Cells dialog box.
Alternatively, you can use the keyboard shortcut Ctrl + 1 to display the Format Cells window.

Thus, the Format Cells dialog box will appear.
Next, go to the Number menu and select Text.
Further, click on the OK button to close the dialog.

Now, if you enter any fraction number, this won’t change.

Finally, entering any fraction number into the selected cells just 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 we supplied.

2. Halting Conversion of Numbers to Dates Using Apostrophe in Excel

The best technique to ensure that numbers look the same after being entered is to use the apostrophe. In this manner, if the formatting is changed back to General and a cell is edited, it will retain its previous appearance rather than being automatically formatted. So, let’s take a view of the steps down.

STEPS:

In the first place, select the cell where you want to put the fraction number.
Then, add an apostrophe(‘) before entering the number.
Press Enter.
This won’t show in the cell but if you look at the formula bar, the apostrophe will show up.

That’s it! You can do this for all the range of cells, adding an apostrophe will prevent Excel from changing the format.

3. Adding Space to Stop Excel from Changing Numbers to Dates

We can prevent Excel from changing numbers to dates by adding a space before entering a number. Let’s add space by following the steps.

STEPS:

Choose the cell where you wish to enter the fraction number first.
After that, use a space before the number.
Hit Enter, the gap is still present in the cell.

That’s all! You can do this for every cell range, and by adding a space, Excel won’t change the format.

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

Before entering a fraction, such as 3/13 or 12/8, ensure to include a 0 and space to prevent it from changing numbers to dates. Let’s follow the steps down.

STEPS:

First, select the cell where you wish to enter the fractional number.
Then, include a 0 and a space before the number.
Furthermore, press Enter.

When you 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.

But there is a problem with this method, 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.

Note: I will suggest using the other methods instead of this, but this will work properly if you use ‘–’.

5. Applying Excel VBA to Prevent Automatic Conversion

With Excel VBA, users can easily use the code which acts as an Excel menu from the ribbon. To use the VBA code to stop Excel from changing numbers to dates, let’s follow the procedure.

STEPS:

First, go to the Developer tab from the ribbon.
Next, click on Visual Basic from the Code category to open the Visual Basic Editor. Or press Alt + F11 to open the Visual Basic Editor.

Instead of doing this, you can just right-click on your worksheet and go to View Code. This will also take you to Visual Basic Editor.

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

This will create a Module in your workbook.
And, copy and paste the VBA code shown below.

VBA Code:

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

After that, run the code by clicking on the RubSub button or pressing the keyboard shortcut F5.

Note: You don’t need to change the code. All you can do is just change the range as per your requirements.

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

Things to Keep in Mind

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

Download Practice Workbook

You can download the workbook and practice with them.

Stop Changing Numbers to Dates.xlsm

Conclusion

The above ways will assist you in stopping Excel from changing numbers to dates. I hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.

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

Get FREE Advanced Excel Exercises with Solutions!

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

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

2. Halting Conversion of Numbers to Dates Using Apostrophe in Excel

3. Adding Space to Stop Excel from Changing Numbers to Dates

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

5. Applying Excel VBA to Prevent Automatic Conversion

Things to Keep in Mind

Conclusion

Related Articles

Leave a reply Cancel reply