Nov 23, 2015

Before I explain why Excel might turn a date into a number I need to discuss how Excel stores data.

When you type information into a cell in Excel, the program tries to work out what type of data you have entered and then stores it as that data type.

When you type a piece of text into Excel it shows that text on the left-hand side of the cell by default. When you type a number or a date into Excel, Excel stores it on the right hand side of the cell by default.

Excel treats text and numbers very differently, you can multiply numbers (and do a heap of other calculations on them) but you can’t do that same thing with text.

If you happen to type what you thought was a number into Excel and it stores it on the left-hand side of the cell then that may be Excel’s subtle way of saying “that’s a lovely piece of text, but to me it is not a number”.

While you can force the alignment of a cell by using the Left / Centre / Right buttons this doesn’t change how Excel thinks of the data in the cell. Notice in the screenshot above, none of the Left / Centre / Right alignment buttons are pushed in, so Excel follows its natural inclination of text on left, numbers on the right.

Also, when you type in a piece of data, Excel tries to automatically format the cell to suit the data you typed in. For example, if you type $500 into a cell, Excel will work out this is a number and store 500 in the cell but also format the cell to currency format because you put a $ at the start.

Before:

Typed $500 into the cell but haven’t pressed Enter. Notice the format on the cell is General.

After:

Pressed Enter. Notice the cell format is now Currency and that there is 500 in the formula bar (not $500).

So now you know that Excel may format the cell when you initially put some data in the cell. It will only change General format, it won’t change other formats.

Want to type a number, like a mobile phone number, into Excel have it treat it like a text so that Excel doesn’t remove the leading zero?

Two ways:

1. Put a single talking mark ('), or if you want to be fancy “an apostrophe” in front of the number like so:

'0456789876

2. Format the cell to Text format, this means that Excel won’t try to convert what it thinks is a number into number format.

In both cases, Excel will display the ‘number’ on the left hand side of the cell. Excel doesn’t see it as a number, it sees it as a piece of text.

When you type a date into Excel, you will notice that it stores the date on the right hand side of the cell.

Excel doesn’t store dates as you see them on the screen, Excel stores dates as numbers. The number of days since the “starting date”.

What is the “starting date” I hear you ask? Day 1 is 1/1/2021 and as of the year 2015 we are up to around day 42,000.

So if you type a date into Excel, Excel stores it as a number. If the cell already had a number format on it then Excel doesn’t change that, and it shows you the date as it sees it, as a number.

The number isn’t wrong, you can change it back to a date by formatting the cell with date format. The reverse also holds true, if you type a number into a cell with date format, Excel will show it as a date, the number of days from the 1/1/1900.

Now that you know how Excel does things, try this: in a new sheet type today’s date (the keyboard shortcut to do this is “Ctrl ;”). Format the cell to Number format and you will see how many days it has been since 1/1/1900. Format the cell back to Date format and you’ll see it as the date again.

For more information, take a look at New Horizons' Excel training courses.

How do your Excel skills stack up?