Excel by default tends to make it easier to enter dates. It is preprogrammed to handle numbers as Dates and convert them accordingly. Number in formats, separated by a backslash (/) or by a hyphen (-), are automatically converted to dates by Excel. For example, 13/6 or 13-6 gets converted to 13-Jun automatically by Excel.
But we don’t always need the default conversion to take place. At times users need to store general numbers separated by a ‘/’ and do not want to convert to the preprogrammed DATE format.
Recommended Read: How to Change Date Format in Excel
Since there is no direct way or option to switch this off in Excel, let’s look at the steps to make this happen.
Steps to avoid conversion of numbers to dates
But we can perform a bit of tweaking to avoid this automatic conversion of numbers to dates in Excel, and that is by using formatting options. Follow the below step-by-step guide to performing that easily.
1. Format Option (most relevant)
All the above-discussed methods to avoid conversion can only be performed if Data is either to be entered manually (no copy-paste) or when the data set to be inputted is small.
This method enables us to stop the conversion of not only a single cell but columns and rows at a time, by selecting all as per requirement.
Select the cell or cells in which you want to perform, and then open the ‘Format Cells‘ option menu by:
- Press Ctrl + 1 or Go to the ‘Home’ tab
- Then go to ‘Format’ drop-down menu
- Now select ‘Text‘ in the pop-up option menu
- Click ‘OK‘ to apply
The final result of this formatting after clicking OK would be –
2. Use a space
Add a space before inputting the number in the cell in any desired format, to stop excel from the automatic conversion of your input number to Excel.
Then press Enter. Even after pressing Enter, the inputted space remains there in the cell.
3. Use an apostrophe(‘)
To avoid conversion, you may even use apostrophes.
Input an apostrophe (‘) sign before the inputted number and press Enter. This apostrophe would not be visible in the actual data set.
For example: ’23/2 results in 23/2 only. Without the apostrophe and even without conversion.
4. Use 0 + ‘ ‘
Input ‘0’ followed by a space, before the inputted number, to avoid automatic conversion of the number.
For example, 0 3/2 results finally to 3/2.
Note: This method converts the cell input to fraction type.
That’s it. We’ve learned how to stop Excel from changing Numbers to Dates automatically and get our inputted value in the desired format.
We hope you learned and enjoyed this lesson and we’ll be back soon with another awesome Excel tutorial at QuickExcel!