Stop Excel from changing Numbers to Dates automatically

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.

Number automatically changed to date in 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

Choose Text formatting option in Excel — Choose Text formatting option

The final result of this formatting after clicking OK would be –

Final Result of formatting in Excel to change number from Date to text — Final Result of formatting

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.

adding space before the number in Excel — adding space before the number

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.

adding apostrophe before the number in excel — adding apostrophe

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.

adding zero and space before number — adding zero and space before a number

Note: This method converts the cell input to fraction type.

Conclusion

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!