Mahbubur Rahman
To make the data entry procedure more convenient, Excel converts some integers or strings to dates. Excel, on the other hand, does so without asking you, and there is no way to turn it off. It’s a well-known problem with Excel that may be aggravating and time-consuming. We’ll teach you how to Disable Auto Convert to Date in Excel in this post. For your better understanding, we will use sample data containing Name, Purchased Item, Purchased Amount, and Purchased (Date/Month).
This is a simple dataset to demonstrate you the methods of disabling auto date conversion. So, we didn’t populate a couple of columns. We’ll populate them along with you throughout the article.
Download Practice Workbook
2 Simple Ways to Disable Auto Convert to Date in Excel
We will see how to disable auto-convert to date using characters and text formatting also see the appropriate formatting for our data.
Method 1: Disable Auto Convert to Date Using Leading Characters
We will use characters like Space, Apostrophe, and Space and Zero together to stop Excel from interpreting our data as a date.
Method 1.1: Disable Auto Convert to Date Using Apostrophe
Suppose, we want to add an amount in our Purchased Amount (Kg) column. We want data 1/2 Kg in cell D5. So, type 1/2 in D5 and press ENTER key.
But, you can see, it automatically converted to date. This is not the result we were expecting.
Now, what should you do? Simple, follow the instructions below.
Steps:
- Type ‘1/2 instead of 1/2
- Now, press ENTER key.
That’s it, we have prevented Excel to convert a value into a date.
Method 1.2: Disable Auto Convert to Date Using Space
Instead of typing apostrophes, we can also use a space. See the image below.
Here, we have inserted a space ahead of the value. After that, pressing the ENTER key, we will get the result we wanted.
Method 1.3: Disable Auto Convert to Date Using Zero and Space
Using Zero and Space together is quite handy while disabling auto-convert to date Excel. It not only stops auto converting but also gives the number in another formatting which could be the one we want.
When we type 1/2Â in cell D5, we get the result as 2- Jan which is not expected. Rather, we want a fraction amount.
Steps:
- First, type a zero then space, and finally the number we want as input data.
- After typing, press ENTER key and we will get the following result.
Here, we not only resolved the date use, but we also got numbers as fractions, which is the actual value.
Now, following the methods, you can input values that will not be converted into dates.
Read More: Convert Serial Number to Date in Excel (7 Easy Ways)
Similar Readings:
- How to Convert SAP Timestamp to Date in Excel (4 Ways)
- Convert Unix Timestamp to Date in Excel (3 Methods)
- How to Convert Text Date and Time to Date Format in Excel (7 Easy Ways)
- Convert Active Directory Timestamp to Date in Excel (4 Methods)
Method 2: Disable Auto Convert to Date Using Text Formatting
In this method, we will see the use of Text Formatting. Suppose, we want to enter the date and month in our column. So, we simply type 1-12 in cell D5.
When we press ENTER key, it yields the result as follows.
So, what is the solution? Follow the steps below.
Steps:
- Select all the cells and press CTRL+1
- A new dialogue box will pop up and we will select Text from there as shown in the image below.
- Now, type your number and we will see the date conversion is disabled.
Let’s populate the column, you’ll find the value in a format exactly what you wanted.
Read More: Text Won’t Convert to Date in Excel (4 Problems & Solutions)
Things to Remember
You can also change integers like 1/2 which could be misinterpreted to 0.5. (or 0,5, if that is your local number format).
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.
Conclusion
These are 2 simple ways to disable auto-convert to date in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.
