Automatic Formatting

Have you ever noticed automatic number formatting when you enter new data or create new formulas?
It is a very subtle feature and most of us probably don't even notice when it happens.
This automatic formatting can appear in a number of different places.
Sometimes it can be useful, other times it can be quite frustrating.
The most annoying time though is when you use the Text to Columns feature or when data is imported into Excel from other systems and it automatically converts strings to dates.

Text to Columns

The way a date is displayed on a worksheet depends on the number format applied to that cell "before" the date is entered.
When you type a string (that Excel thinks is a date) and the number format is "General" then the text string can be converted to a date (or time) automatically and the number format changes accordingly.
The only official article that we found relating to this feature was Knowledge Base 214233.
It states: "if a number contains a slash mark / or a hyphen - , it may be con verted to a date format.
If a number contains a colon : or is formatted by a space and the letter A or P, it may be converted to a time format"

Let's see an example, select cell "B2" and check that the number format is "General".
Type 09-01-26 into this cell and press Enter.
Notice that it changes automatically to 09/01/2026.
In fact what has actually happened is that the value has been automatically converted into a date and the number format has been changed accordingly.
Select the Format Cells dialog box and you will see that the number format has been changed to "dd/mm/yyyy".
Even if you change the number format back to "General" or "Text" you cannot get back to your original string. The value remains 46031.

There are a number of specific character combinations which Excel will translate into either a date or time.

1/7 = 01-Jul (dd-mmm) 01-07 = 01-Jul (dd-mmm) 01-07-11 = 01/07/2011 (dd/mm/yyyy) 1 Jul 26 = 01-Jul-26 (dd-mmm-yy) 1-7 = 01 Jul (dd-mmm) 1/1/26 = 01/01/2026 (dd/mm/yyyy) Jul-1 = Jul-01 (mmm-yy) 12:25 = 12:25 ("hh:mm") 12 A = 12:00 AM (h:mm AM/PM) 11 P = 11:00 PM (h:mm AM/PM)

Worksheet Functions

Automatic formatting can also appear when you use worksheet functions.
There are a number of worksheet functions which, when used, will result in the number format of that cell changing automatically.
Select cell "C2" and check that the number format is "General".
Type the formula "=DATE(2014,4,1)" and press Enter.
Select the Format Cells dialog box and look at the number format.
It has been changed to "dd/mm/yyyy".

Formulas

Automatic formatting can also appear when you are using formulas.
When you enter a formula into a cell which has the "General" number format.
That number format is changed automatically to match the number format in the referenced cells.
If your formula refers to multiple cells that have different number formats applied to them Excel will use its own rules to decide which number format to apply.
Once a number format has been changed from "General" to something else, it will not be changed again.
Changing the cell value or the number formatting of referenced cells will have no effect.

Cell "B3" contains a date and has the number format "dd/mm/yyyy".
Cell "D3" contains a value and has the number format "General".
Select cell "F3" and check that the number format is "General".
Type the following formula "=B3+D3" and press Enter.
Select the Format Cells dialog box and you will see that the number format has been changed to "dd/mm/yyyy".
There is no way of switching this feature off.
Some more information can be found in the Knowledge Base 214233 article.

VBA Code

Automatic formatting can also occur when you populate cells using VBA code.
When you type "01/07/2014" into a cell, this gets recognised as a date automatically and the number format gets changed to "dd/mm/yyyy".
You might expect the following two subroutines to give us the same result, but they don't.
In both of these situations a date is recognised but it is assumed that it has been entered in the US date format of "mm/dd/yyyy".
If you want to enter dates using VBA code then you need to convert the date to its date serial number first before adding it to the worksheet.
Once the serial number has been added you can apply your date format.