Leading the way in Microsoft Office Development

Step 1 - Entering Valid Dates

All date serial numbers start from 1st January 1900 by default.

When a date has not been formatted with a date number format the serial number for that date will be displayed.

When you enter a valid date the value in the cell is automatically converted to the corresponding date serial number and the cell is formatted to your short date style.

For example if you enter the date 01/07/77, the value is automatically converted to the serial number 28307 and the date number format "dd/mm/yyyy" is automatically applied.

Step 2 - Displaying Dates

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

When you select a cell that contains a date the date is displayed in the formula bar. Using the "short" date style defined on your PC.

The only way to obtain the serial number corresponding to a date is to format the cell with a non date or time number format.

To view a date serial number as an actual date you can apply a date number format using the (Format > Cells)(Number tab) dialog box.

You can enter a date using any of the recognised date formats.

The table below shows some dates with their corresponding serial numbers.

Note: *1900 was not a leap year, although Excel thinks it is, this is discussed in more detail later on.

Step 3 - Default Formats

When you enter a date into a cell a date number format will be applied automatically.

The date number format will only be changed if the cell contains the "general" number format.

Notice that the default varies slightly depending on how the date is entered.

Note: *When a year is not entered as part of a date the current year is assumed. When a day is not entered the first of the month is assumed.

Step 4 - Entering Invalid Dates

If you enter a date in a format that is not recognised, then the value is just interpreted as text and is displayed as text.

If you enter a date outside the supported date range (1 Jan 1900 - 31 dec 9999), then the value is interpreted as text.

If you apply a date or time number format to a negative serial number or a number greater than 2958465 (i.e. 31 Dec 9999) then ###### will be displayed.

The only way to use dates that are before 1900 is to enter them as text. You will be unable to manipulate and perform calculations on these dates.

Step 5 - Abbreviated Dates

Any year from 0-29 is considered to be 2000-2029 and any year from 30-99 is considered to be 1930-1999.

To avoid any risk of getting the wrong date you should always enter the years as a four digit value. This can easily be formatted afterwards if Excel displayed it in full.

Step 6 - Manipulating Dates

It is possible to perform calculations with your dates because they are represented as sequential whole numbers.

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

This means that dates can be subtracted from one another as well as units being added to dates.

Every time you reference a date or time in your formula the cell containing the formula will have its number format automatically changed to a date or time specific format.

Step 7 - Using AutoFill

For more details about entering dates using the AutoFill feature, please refer to the Fill Series page.

Step 8 - Things to Remember

The last date that Excel will recognise is 31st December 2078.

You can enter the current date as its serial value by typing "=TODAY()".

When a year is not entered as part of a date, the current year is assumed.

When you enter an abbreviated year as a two digit number, if the value is between 0 and 29 then 2000-2029 is used. If the value is between 30 and 99 then 1930-1999 is used.

If you enter a valid date and Excel does not recognise it, then you should check the regional settings on your PC.

You can change your regional date settings by opening the Control Panel, choosing Regional Settings and selecting the Date tab.