Related Tutorials
Date Formatting
Some common problems can occur whilst using dates within Excel, and this is due to the way in which a date is stored. If using dates in your spreadsheets, it is important to grasp an understanding of how Excel uses dates. If not it could result in inaccurate data.
Excel does not store a date as we know it, but as a serial number.
This serial number is how many days have passed since the 31/12/1899, for example the number 40004 is the date 10/07/2009.
This is done so that formulas can be performed on cells containing dates. For example, to find out the number of days passed between two dates, we subtract one date from the other.
Custom date formatting
Excel provides a list of different date formats for your use. But this list is not endless and you may wish to create your own custom format.
Codes are used to alter the display of days, months and years for a date.
Custom date format examples
Format |
Result |
|---|---|
dddd d mmmm yyyy |
Thursday 27 August 2009 |
dd mmmm |
27 August |
"Today is "dddd |
Today is Thursday |
dd/mm/yyyy |
27/08/2009 |
It is recommended to display 4 digits for the year to avoid any confusion.
Follow us on
