Excel Dates and Times

If you want to work with Excel dates and times, it is useful to understand the way that Excel stores dates and times.

Dates and times are actually stored as simple numbers within Excel. It is only the Formatting of a spreadsheet's cells, that causes a number to be displayed as a date, time, or date & time.

Within Excel, dates and times are coded as follows:

Dates

Dates in Excel are stored as positive integers.

For example, on most computer systems:

1=1st January 1900
2=2nd January 1900
40209=31st January 2010

Therefore, when entered into a spreadsheet, the integer values, 1, 2 and 40209 are displayed as follows (depending on the cell formatting):

 With 'General'
Formatting:
 A
11
22
340209
 With Date Format
"dd/mm/yyyy":
 A
101/01/1900
202/01/1900
331/01/2010
Times

Times in Excel are stored as decimals, between 0 and 1, which represent a proportion of the day.

For example:

0=00:00 hrs
0.25=06:00 hrs
0.5=12:00 noon

Therefore, when entered into a spreadsheet, the decimal values, 0, 0.25 and 0.5 are displayed as follows (depending on the cell formatting):

 With 'General'
Formatting:
 A
10
20.25
30.5
 With Time Format
"hh:mm":
 A
100:00
206:00
312:00
Dates & Times

Date & Time values in Excel are stored as decimals, comprised of:

  • an integer (representing the date)
and
  • a fraction between 0 and 1 (representing the time).

For example, on most computer systems:

1.5=12:00 hrs on 1st January 1900
40208.25=06:00 hrs on 30th January 2010
40209.5=12:00 hrs on 31st January 2010

Therefore, when entered into a spreadsheet, the above values are displayed as follows (depending on the cell formatting):

 With 'General'
Formatting:
 A
11.5
240208.25
340209.5
 With Date & Time Format
"dd/mm/yyyy hh:mm":
 A
101/01/1900 12:00
230/01/2010 06:00
331/01/2010 12:00


Adding and Subtracting Dates and Times in Excel

Because Excel stores dates & times as numbers you can add, subtract and compare dates and times in Excel in the same way that you would add or subtract any other numbers.

Examples are provided below:

Example 1 - Calculate the Number of Days Between Two Dates

 AB
120/08/2010=A2-A1
231/08/2010

Cell B1 of the above spreadsheet on the right uses the formula =A2-A1 to calculate the number of days between the dates 20/08/2010 & 31/08/2010 (stored in cells A1 and A2).

This gives the result 11.

Example 2 - Calculate the Difference Between Two Times

 AB
113:00=A2-A1
217:15

Cell B1 of the above spreadsheet on the right uses the formula =A2-A1 to calculate the number of hours and minutes between the times 13:00 & 17:15 (stored in cells A1 and A2).

This gives the result 04:15.

(Note that, in order to display this result, cell B1 should be formatted to have the time format "hh:mm").

Example 3 - Calculate the Difference Between Two Dates and Times

 AB
130/08/2010 17:00=A2-A1
231/08/2010 09:00

Cell B1 of the above spreadsheet on the right uses the formula =A2-A1 to calculate the number of hours and minutes between the two dates & times stored in cells A1 and A2.

This gives the result 16:00.

(Note that, in order to display this result, cell B1 should be formatted to have the time format "hh:mm").

Example 4 - Add a Time to a Date and Time

 AB
130/08/2010 17:00=A1+A2
223:00

Cell B1 of the above spreadsheet on the right uses the formula =A1+A2 to calculate the date and time that is 23 hours after the date & time 30/08/2010 17:00.

This gives the result 31/08/2010 16:00.

(Note that, in order to display this result, cell B1 should be formatted to have the date and time format "dd/mm/yyyy hh:mm").


Common Problem

Formatting Dates and Times in Excel

The results in of the above Excel date and time formulas may not have the required formatting when first input into your spreadsheet. This may give the initial impression that the formula has not worked - however, this is not the case.

The cells can be made to show the correct result by formatting into a 'General', 'Date', 'Time', or 'Date & Time' format, depending on the required result type.

The easiest way to change the formatting of one or more Excel cells is to select the cell(s) to be formatted and then select the required formatting type from the drop-down menu in the ribbon. This is generally found in the 'Number' group on the Home tab (see below):

Excel Format Cells Drop Down Menu on Ribbon With Time Option Selected

If you don't see the formatting style that you require, select the option More Number Formats ... from the drop-down format menu. This opens up the 'Format Cells' dialog box, which provides several additional pre-programmed formats or allows you to define your own Custom format.


Click here for more details on Excel formatting