Stop Auto Formatting / Changing Numbers to Dates in Excel & Google Sheets

This tutorial demonstrates how to stop auto-formatting numbers and changing them to dates in Excel and Google Sheets.

 

 

Stop Auto-Formatting Numbers as Dates

In Excel, if you type in numbers that can be interpreted as dates, they’re automatically converted to dates. This makes it easier to deal with dates but can be annoying when you really want to enter numbers, not dates. For example, if you enter a fraction of 3/4, it’s displayed as 4-Mar (3/4/2021), or 1-15 is 15-Jan.

For example, enter 3/4 in cell B2 and press ENTER.

 

 

As soon as you press ENTER on the keyboard, Excel automatically converts the fraction into a date. There are several options to avoid this and keep the value in the cell as it is entered.

Enter a space before the number.

This prevents Excel from converting the value to a date, but there will be issues if you try to use a function like the VLOOKUP Function. In that case, the function won’t find the value – stored as text – because of the space at the beginning.

 

 

Enter an apostrophe (‘) before the number.

The apostrophe won’t be displayed in the cell, and the value in the cell, stored as text, displays exactly as typed.

 

 

Enter a zero and space before a fraction.

In the case of fractions, a zero before the fraction is interpreted as a number, so 0 3/4 is equal to 0.75. In this case, Excel automatically converts the value and format to the Fraction type.

For example, type “0 3/4” in cell B2.

 

 

After you press ENTER on the keyboard, the cell value is converted to the fraction type (0.75). Only this option saves the entry as a number.

 

 

Stop Auto-Formatting Numbers as Dates in Google Sheets

In Google Sheets, the only way to stop formatting numbers as dates is to use an apostrophe in front of the value. The other two options that are useful in Excel are not valid in Google Sheets.