Why does Excel keep changing my numbers to dates

Question

How to stop Excel from automatically changing numbers into anything other than what you typed, such as changing a fraction into a date when you enter it into a cell.

Table of Contents Show

Apostrophe Method
Format as Text Method
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Download Practice Workbook
5 Effective Ways to Stop Excel from Changing Numbers to Dates
1. Utilize Format Cells Feature to Prevent Excel from Converting Numbers to Dates
2. Halt Conversion of Numbers to Dates Using Apostrophe in Excel
3. Add Space to Stop Excel from Changing Numbers to Dates
4. Stop Automatic Change from Numbers to Dates by Inserting Zero & Space
5. Apply Excel VBA to Prevent Automatic Conversion
Things to Keep in Mind
Related Articles

Apostrophe Method

Using the apostrophe is the preferred way to keep numbers looking the same after you enter them, at least as far as I am concerned. This way, if someone changes the formatting back to General and goes to edit a cell, it will still look the same way instead of being auto-formatted.

Type an apostrophe in the cell:

Then type the number and press Enter:

Notice that the apostrophe does not appear in the cell after we press Enter; however, if we go to edit the cell, you can see that it is still there:

Here is a sample list:

If we entered them without an apostrophe, they would have looked like this:

Using this method, we will also still be able to use MATCH and VLOOKUP with the numbers as Excel will ignore the apostrophe.

Format as Text Method

If you know that you will be entering a list of numbers, simply format the cells as Text before you go to enter the data. This way, everything will look exactly the same as how you typed it.

Select the desired cells > go to the Home tab and click the drop down menu in the Number box and select Text:

Enter what you want:

If we just entered the numbers regularly, we would have gotten this:

Notes

Both methods will work. I prefer to use the apostrophe method since it's harder for a user down the line to mess it up.

Make sure to download the sample workbook that accompanies this tutorial so you can better see how this works in Excel.

Download Practice Workbook

You can download the workbook and practice with them.

5 Effective 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. Utilize 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.

Read More: [Fixed!] Why Is Excel Changing My Numbers? (4 Reasons)

2. Halt 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.

Read More: [Fixed!] Excel Changing Dates to Random Numbers (3 Solutions)

3. Add 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.

Read More: How to Stop Autocorrect in Excel for Dates (3 Quick Ways)

4. Stop 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 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 ‘–’.

Read More: How to Stop Excel from Changing Numbers (3 Easy Methods)

5. Apply 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:

Firstly, go to the Developer tab from the ribbon.
Secondly, 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.
Thirdly, 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.

Read More: How to Stop Excel from Auto Formatting Numbers (3 Easy Ways)

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.

Conclusion

The above ways will assist you to Stop 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. Or you can glance at our other articles in the ExcelDemy.com blog!