Excel Date

You can display Excel dates in different ways in your cells.

However, the way that Excel stores dates internally is always the same.

How Excel Stores Dates

Each date stored in Excel represents the number of days and fraction of days since January 0 1900. The number of days is a whole number whereas the time is a decimal fraction. The format of the Excel date is ddddd.tttttttt. This means, for example, that midday on January 1 1900 is represented as 1.5 as an Excel date. 1 because the date is 1 day after Jan 0 and .5 because midday is 12 hours into the 24 hours each day has (12/24 = .5). In effect, January 0 1900 is the start of the Microsoft Excel calendar and this representation is called a serial date. Note that 0 does not represent December 31 1899. 0 is simply a convenient starting point that does not actually represent a date. Conveniently, the Excel date can be expressed as the sum of the date and time. Also, because dates and times are stored as numeric values, they can easily be added and subtracted. If you have entered a date and time into a cell with a date format, you can see the serial date by changing the cell's format to general.

If you enter only a time into a cell, the date portion is stored as 0. Excel does not default the current date. Thus, if you enter 18:30 into a cell, the Excel date is Jan 0 1900 18:00.

1904 Date System

There are actually two ways that Excel can store dates. The first is described above: as the number of days since January 0 1900. The second is the number of days since January 0 1904. This second way is called 1904-mode or 1904-system and is employed by Excel to enable compatibility with Macintosh systems.

Excel Date Format

Whether 2 digits or 4 digits are used to represent the year portion of your dates depends on what your system settings are. To change them do the following:

Changing The Date Format In Windows XP

  • Click Start > Control Panel
  • Click Regional and Language Options
  • Go to the Regional Options tab
  • Click Customise
  • Go to the Date tab
  • For the Short date format, select the appropriate options from the drop down list
  • Click Apply, then click Apply again

Changing The Date Format In Windows Vista

  • Click Start > Control Panel
  • click Clock, Language, and Region
  • Click Regional and Language Options
  • In the Regional and Language Options dialog box, click the Formats tab
  • Click Customize this format
  • Click the Date tab.
  • In the Short date format list, click a format that uses the required format

If your system is set up to display 4 digit dates and you enter into a cell the date 21/01/10, Excel will convert it to 21/01/2010.