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.


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.

how to stop excel from changing numbers to dates


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.

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

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 ‘’.

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

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

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

Halt Conversion of Numbers to Dates Using Apostrophe in Excel

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

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

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

Apply Excel VBA to Prevent Automatic Conversion

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


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo