It is inevitable that every user of Excel software has faced this problem at some point, if not daily. Whether you are a data scientist or just a regular user navigating your way through the popular Office program for the first time, it is very likely that you will notice that the numbers you enter are automatically converted to dates without any warning. For example, if you type 11/30 as a decimal number in one of the Excel cells and press Enter, the program will convert this number to the date 30th of November!
The same thing happens if you enter a long number, where it becomes a date of 3 digits (day, month, year). If you are studying a field related to biology or genetics, for example, you are more likely to suffer from this problem; as there are genes that have symbols close to dates like SEPT2 and MARCH1, and therefore the program interprets them as month names and converts them automatically. However, there are simple ways to get rid of this nonsense and make Excel behave wisely with numbers; let us tell you how.
Stop Converting Numbers to Dates in Excel
According to a study published in 2016, about one-fifth of the studies and scientific papers published contain incorrect information or data due to these problems caused by Excel. It reached a point where scientists started renaming genes to avoid such errors. After years of user complaints, Microsoft finally decided to address the issue officially, by announcing last October the addition of an option in Excel settings to prevent the program from converting numbers and symbols to dates.
Unfortunately, this new option will not be available in all Microsoft Office versions, but only in the Microsoft 365 office package in addition to regular Microsoft Office versions starting from Office 2024. However, for most of us, this is not a hurdle; using academic email alone, you can get a free subscription and enjoy all the programs in the package, which is personally what I use. Or you can take advantage of the free trial offered by Microsoft to new subscribers.
Method One: Stop Automatic Data Conversion
Open Excel program and from the side menu, click on Options at the bottom. A program settings window will now appear, go to the Data section from the menu on the left. You will now see 3 main sections, the last one is called “Automatic Data Conversion” and it has several settings. Here, if you want to just prevent the program from converting numbers and letters to dates, you can disable the “Convert continuous letters and numbers to a date” option by unchecking it and then click on “OK” to save the changes.
It is worth mentioning that converting numbers to dates is not the only thing that Excel program does automatically, as the program sometimes deletes zeros at the beginning of numbers, for example, if you enter a value like “00328” and press Enter, you may notice that the two zeros have been removed. Not only that, Excel also sometimes replaces the letter “E” because it has a mathematical meaning of 10 raised to a certain power, and repeated numbers, in addition to shortening decimal numbers. For each of these dilemmas, you will find a specific setting in the same section of Automatic Data Conversion, and you can disable all automatic conversions by unchecking the option “…Enable all default data conversions below when”.
Method Two: Adding an Apostrophe before the Number
This is the common solution to get rid of the automatic conversions done by Excel. Just by adding an apostrophe before the value. The apostrophe will not appear in the cell itself after pressing Enter, but it will make Excel treat the value after the apostrophe as text. Simply enter the apostrophe (by pressing the (‘) key on the keyboard after switching the writing language to English) and then enter the value. Example: [1/2‘] then “Enter” and you will find that the value has been saved numerically as “1/2”.
In the end, it is important to know that preventing Excel from converting numbers automatically to dates may lead to some problems or inaccurate calculations in some large or complex operations, so my advice is to try it yourself and review the results more than once.
