Excel Dates and Times

Most users have worked with Excel dates and times, to some extent. However, not all users fully understand the way that dates and times are stored within Excel, and this prevents them from getting the most out of their Excel functions and formulas.

Excel 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:


Stored as positive integers.

For example, on the windows operating system:

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

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
Dates & Times

Stored as decimals, comprised of

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

For example, on the windows operating system:

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

Adding and Subtracting Dates and Times in Excel

330/08/2010 17:0031/08/2010 09:00
430/08/2010 17:0023:00

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

The following examples use the date and time values in the example spreadsheet on the rightabove to illustrate the addition and subtraction of dates and times in Excel.

Example 1

  • Calculates the number of days between 20/08/2010 & 31/08/2010 (in cells A1 and B1)
  • This gives the result 11

Example 2

  • Calculates the number of hours and minutes between 13:00 & 17:15 (in cells A2 and B2)
  • This gives the result 04:15

Example 3

  • Calculates the number of hours and minutes between 30/08/2010 17:00 & 31/08/2010 09:00 (in cells A3 and B3)
  • This gives the result 16:00

Example 4

  • Calculates the time that is 23 hours after 17:00 on 30/08/2010
  • This gives the result 31/08/2010 16:00

Common Problem

Formatting Dates and Times in Excel

The results in of the above Excel date and time formulas will generally NOT have the correct formatting when first input into your spreadsheet. This will 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.

To do this :

  • Highlight the cell(s) with the wrong formatting
  • Right click with the mouse
  • Select the Format Cells ... option and ensure the Number tab is selected
  • Under the Category heading, select the required formatting style and click OK

Click here for more details on Excel formatting