Leading the way in Microsoft Office Development

Understanding Dates

In Excel dates represented as sequential whole numbers.

Excel keeps track of dates by assigning each date with a serial number.

These whole numbers are often referred to as "serial numbers", since they just represent the number of days since 1st January 1900.

A date can be displayed in many different formats, but the value which Excel uses and stores in the cell is the serial number.

Dates start from 1st January 1900 with the serial number for a particular date being the number of days since this date.

A serial number of 3 corresponds to the 3rd January 1900.

Excel can only perform date calculations on dates after 1st January 1900. Any dates typed in before this are not recognised and will be automatically converted to text. These cannot be used in any formula calculations.

Representing dates using numbers means that dates can be easily manipulated and used in formulas (i.e. added and subtracted).

Understanding Times

In Excel times are represented as decimal fractions.

Any 12hr times entered are assumed to be AM unless you explicitly type PM.

Times are treated as fractional parts of a day.

Numbers to the right of the decimal point in a serial number represent the time;

Numbers to the left of the decimal point in a serial number represent the date.

For example, the serial number 367.5 represents 1st January 1901 at 12:00 PM.

These decimal fractions can then be added to the date serial numbers to identify a particular time on a particular date.

When you select a cell containing a date, the formula bar will display the date in the default date style.

Entering Dates & Times

When you enter a date the cell will automatically be formatted to your short date style defined in your Regional Settings. This is likely to be either "dd/mm/yyy" or "d mmmm, yyyy".

To view the actual serial number of a date you must change the number format to anything other than a Date or Time specific format.

When dates and times are entered correctly they are aligned on the right by default (since they are just numerical values).

When you eneter a date you can either enter it using a two-digit format or the full four-digit format. Using the four-digits is safer.

00-29 - 2000 to 2029

30-99 - 1930 to 1999

If you enter an invalid date it is interpreted as text and is aligned on the left by default.

You can enter the current date as a serial value by using the "=TODAY()" function.

You can enter the current date and time by using the "=NOW()" function.

You can enter the current time as a decimal value by using the following formula "=NOW()-TODAY()".

There are a number of different formats that can be used to enter your dates.

Entering a date in any of these formats will mean a date number format is automatically applied.

Worksheet Functions

There are 21 date and time functions that can help you to perform your calculations quickly and accurately.

Seven of these functions are included in the Analysis Toolpak addin.

When passing dates into functions, these can be as text strings within quotation marks, as a serial number or as the result from other functions or formulas.

Are the dates returned from functions always left aligned ??? As if they were text

Using AutoFill

Lets you quickly insert series of dates and numbers

For more detail on this please refer to the Copying Dates page.

1904 Date System

Excel actually supports two dates systems: A 1900 date system and a 1904 date system.

The 1904 date system is the default if you are using Excel for Macintosh.

The 1900 date system is the default if you are using Excel for Windows.

All dates start from 1st January 1900. This is the default. You can change the start date to 2nd January 1904 if you wish. Select (Tools > Options)(Calculation tab, "1904 Date System").

This date system is workbook specific.

Things to Remember

All the dates from 1 Jan 1900 to 31 Dec 9999 are supported.

All dates on this website are in the UK date format dd/mm/yy and not in the United States English date format mm/dd/yy.

When dates and times are entered correctly they are right aligned by default (since they are just numerical values).

If you want to use your dates in formula then they must be recognised as dates and not as text.

You can change your default short and long date options by accessing the Control Panel and selecting Regional and Language Options.

If you are using Excel on a network, make sure the date has been set up correctly if you are seeing the incorrect dates.