How to stop Excel from auto-formatting dates

You can watch a video tutorial here.

Excel is frequently used for calculations and has many functions to help with basic mathematical operations.  Excel usually applies the ‘General’ format to values entered in cells, both for letters and for numbers. If a number appears to be a date, Excel formats it as a date. This is usually quite useful but you may want the number to remain a number and not be converted into a date. Here we will look at 2 ways in which this can be done.

Option 1 – Use an apostrophe

Step 1 – Type the value

• Type an apostrophe, followed by the number:

‘1/2

• Press Enter
• The number is displayed as a number and does not get formatted as a date

Note: A space can be used instead of an apostrophe but this will affect the alignment of the vaues

Option 2 – Format the cells

Step 1 – Open the Format Cells window

• Select the cells where the numbers are to be displayed
• Right-click and select Format Cells  from the context menu

OR

Go to Home > Number  and click on the arrow to expand the menu

OR

Go to Home > Cells > Format > Format Cells

OR

Press Ctrl+1

Step 2 – Define the format

• Go to the Number tab
• Choose Text from the list of categories
• Click OK

Step 3 – Check the result

• Type a number such as 1/6
• It will be displayed as such and will not be formatted as a date