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