Dates & Times

In Excel dates are represented as sequential whole numbers.
All the dates from 1 Jan 1900 to 31 Dec 9999 are supported.
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).
Excel considers the 0th day of a month to be the last day of the previous month.

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.

Using AutoFill

Lets you quickly insert series of dates and numbers
For more detail on this please refer to the Fill Dates page.

Volatile Functions

The NOW and the TODAY are examples of volatile functions.
A volatile function is a function whose value is subject to change every time it is calculated.
Every time these functions are calculated the value returned could be different, this is equivalent to manually making a change in the workbook and you will be prompted to save the changes.
Here is a full list of all Volatile Functions.

Adding and Subtracting Times

You can add and subtract times, although there are complications - what are they ??

Automatic Number Formatting

If you create a formula that refers to a cell formatted as a date or time, then the number format of the cell containing the formula will be automatically adjusted to a date or time format.

Negative Dates and Times

You cannot subtract a start time from an end time ??
If the result of a date or time calculation is negative then the cell will be displayed as "#####". You can often work around this by converting the result to elapsed hours. Multiply the result by 24 and display it with a number format as opposed to date or time.
A time without a date has a date serial number of 0 and therefore a negative time results in a negative serial number which is not allowed.
It is important to remember that any negative times are displayed as "#########"

It is possible to subtract times that cross midnight, for example 10pm to 3am.
You can use the MOD() function "=MOD(latesttime - earlier time, 1)
The functions and formulas convert every date and time to serial values, which can then be added or subtracted before being converted back into a recognisable date or time.
Subtracting one date from another involves subtracting one serial value from another and then converting the result back into a date.

Displaying total hours greater than 24

If you are adding up hours and minutes the number will never exceed 24 unless you use the following custom number format "[h]:mm".
How can I format times greater than 24 hours to display the full number of hours? - [h]:mm:ss (or enter it as a custom format).

If you are entering times into cells you can use the custom format 00";"00 to save you entering the colon every time. If you only want the hour displayed as single digit then use 0":"00.

Searching for Dates

It is possible to use the (Edit > Find) dialog box to search for particular dates.
You must however enter the date in the correct format. The only correct date format that can be used is your "short" date style.

1900 was a leap year ?

This is not a leap year but Excel thinks that it is.
This means that Excel thinks the date 29 Feb 1900 exists when it doesn't

Important

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.