Click here for great value web hosting.








Search for



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 Excel’s date and time formatting commands to convert the date value to a more readable format but don’t 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 options.

Click here to return to the Excel index.