Stop Auto Formatting of Numbers into Date in Microsoft Excel

Whenever you eneter numbers separated by a hyphen in Microsoft Excel, it automatically converts that data into date format. Suppose you type “2-2” in Excel it becomes “02-Feb” automatically, so to prevent this automatic formatting of numbers into date in Excel follow simple steps below.

stop-auto-date-format-in-excel

Prevent Excel from Reformatting Numbers into Date

Method-1
Step-1

Select entire cells of a Workbook in Excel by pressing “Cmd” + “A” (Mac), “Ctrl” + “A” (Windows) and then right click on any cell and select the option “Format Cells…” from opened list

format-cells-in-ms-excel

Step-2

Select “Number” tab, and from the left panel select “Text” category

change-excel-cells-format

Now, whenever you will enter numbers in a cell of this Worksheet, that will remain a number and will not be converted into date format.

Note: This change is only applicable for the Workbook/Worksheet in Excel for which it is selected.

Method-2

Simply type an apostrophe before typing numbers in a cell of Excel Workbook and look the effect after leaving that cell, apostrophe will hide automatically and the numbers will display as numbers.

2 comments… add one
Cory July 26, 2013, 9:12 am

Works for Excel 2011 on Mac.

Ozil July 27, 2013, 8:06 pm

Nice to have this tip for Microsoft Excel.

Leave a Reply