Excel Dates and Times

Understanding 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 its contents to be displayed as a date, time, or date & time.

The following table shows how dates and times are coded within Excel:

Dates

Dates in Excel are stored as positive integers.

For example, on most computer systems:

1=January 1, 1900
2=January 2, 1900
42370=January 1, 2016

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

 With 'General'
Formatting:
 A
11
22
342370
 With Date Format
"mm/dd/yyyy":
 A
101/01/1900
201/02/1900
301/01/2016
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 January 1, 1900
2.25=06:00 hrs on January 2, 1900
42370.5=12:00 hrs on January 1, 2016

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

 With 'General'
Formatting:
 A
11.5
22.25
342370.5
 With Date & Time Format
"mm/dd/yyyy hh:mm":
 A
101/01/1900 12:00
201/02/1900 06:00
301/01/2016 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
108/20/2016=A2-A1
208/31/2016

Cell B1 of the above spreadsheet on the right uses the formula =A2-A1 to calculate the number of days between the dates 08/20/2016 & 08/31/2016 (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
108/30/2016 17:00=A2-A1
208/31/2016 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
108/30/2016 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 08/30/2016 17:00.

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

(Note that, in order to display this result, cell B1 should be formatted to have the date and time format "mm/dd/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