Using Date and Time Formats
When Excel stores dates it records
them as a number representing the number of days since 1 January 1900. Your computer should be set up to use the 1900 date system
(see under the Tools drop-down menu and Options and click on the
Calculation tab). An
alternative system that uses 1 January 1904 as day 1 is also available to
provide compatibility with Apple Macintosh users but you should not use this
date system unless you absolutely have to.
The actual number generated by
Excel works out as one greater than the number of days since 1 January 1900,
this is because it treats the year 1900 as having a leap day on 29 February
1900, which it did not. This
mistake was made in Lotus 123 and Microsoft decided to copy it in Excel to
avoid problems for people converting from Lotus 123 to Microsoft Excel.
The time is stored as a decimal
tagged onto the date number so 0.50 added to the date value represents midday.
You can use Excels date and time formatting commands to convert the
date value to a more readable format but dont panic if you accidentally
format a date cell as a number and see a strange value like 38257.63 appear,
this number represents the date and time you had entered.
The date format option in the
Format Cells window gives a variety of formats you can use as shown below
, if these do not meet your exact requirements you can create a custom
format to display a date in the way you want.
If you only want to see the time then use the Time format option and
choose the type of time display you would like.
It is possible to display just date, just time or both using these
Click here to
return to the Excel index.