subject: How Microsoft Excel Stores Dates, and How to Format Dates in MS Excel [print this page] How Microsoft Excel Stores Dates, and How to Format Dates in MS Excel
Dates Don't Exist in Excel
Microsoft Excel appears to have 3 main types of data - text, numbers and dates - but in truth the last two of these are actually one and the same.
To see why, go to a blank cell and type in =TODAY() and press ENTER. Excel should now display today's date (let's say it's 8th January 2011 and you're working in the UK; in this case you'll see the date as 08/01/2011).
Now go to the toolbar and click on the COMMA STYLE tool (it's on the HOME tab of the ribbon towards the middle, along with the other number formatting tools, and - unsurprisingly - bears a picture of a comma on it (,).
Your cell will now show 40,551.00 (or some similar number, depending on which date you saw before). Why? Well, Excel numbers dates from 1st January 1900. 2011 is roughly 111 years after this, which at about 365 days per year gives us a total of111 * 365 = 40,515. Adjusting for leap years and the fact that our sample date is8 days into the new year gives us our final answer.
Formatting Dates
Now that we've established that dates don't actually exist, how can you format them? The easiest way is to go to the FORMAT CELLS dialog box and select the NUMBER tab. You can get to this dialog box in (at least)2 ways:
Right-click on a cell and choose FORMAT CELLS from the short-cut menu; or
Press CTRL + 1
You can now choose the DATE category on the left-hand side, and choose one of the standard dates. However, it's much more powerful to create your own date format. To do this, scroll down the left-hand list of categories to the bottom and select CUSTOM. You can now type any format that you like into the TYPE box, keeping your eye on the SAMPLE area of the dialog box to see what this format will show.
The Date Format Rules
The rules are that you can type in any combination of d, m and y. For each letter, the more you type in, the more detail you will get. This is best shown by example:
dd/mm/yyyy would give 08/01/2001
dddd dd mmmm yyyy would give Saturday 08 January 2011
d m y would give 8 1 11
ddd d mmm yyyy would give Sat 8 Jan 2011
dd mmmmm yyyy would give 08 J 2011 (we see that this is an exception to the rule; when you type a 5th m, you get the first letter of the month only for some strange reason)
