Calculate Time Difference in Excel

Home » Excel-Formulas » Time-Difference-In-Excel

Search this site:

Custom Search

If you want to calculate a time difference in Excel, it is important that you understand that Excel stores dates as integers and times as decimal values (see the page on Excel dates for more details). It is only the formatting of an Excel spreadsheet that causes these values to be displayed as dates and times, rather than as simple numbers.

Therefore, when you want to calculate the time difference between two dates and times in Excel, you can simply subtract these values in the same way as you would subtract any other numerical values in Excel. This is shown in the examples below.

When users have problems with Excel date and time difference calculations, these problems are commonly due to incorrect formatting of the results, rather than incorrect calculations. Therefore, in the examples below, we also discuss the cell formatting that you may require, to correctly display the result of your time difference calculation.

Date and Time Difference Examples

Example 1

Cell B3 of the following spreadsheet shows a simple example of how to calculate a time difference in Excel. The calculation simply subtracts the time in cell B1 from the time in cell B2.

The spreadsheet on the left displays the formula used for the calculation and the spreadsheet on the right shows the result.

Formula:

	A	B
1	Start Time:	03:45:30
2	End Time:	10:17:45
3	Time Difference:	=B2-B1

Result:

	A	B
1	Start Time:	03:45:30
2	End Time:	10:17:45
3	Time Difference:	06:32:15

In the example above, the time in cell B1 is internally represented by the number, 0.156597222 and the time in cell B2 is internally represented by the number 0.428993056. Subtracting these two numbers gives the result 0.272395833, which, when formatted with the time format, hh:mm:ss, is the time 06:32:15 (i.e. 6 hours 32 minutes and 15 seconds).

To Format a Cell as a Time:

-	Right click on the cell to be formatted
-	Select the option Format Cells...
-	Ensure the Number tab is selected in the window that pops up
-	Select the option Time from the list of Categories and click OK

Example 2

The following spreadsheet shows a simple subtraction of two dates and times. Again, the spreadsheet on the left shows the formula used and the spreadsheet on the right shows the result.

Formula:

	A	B
1	Start Date & Time:	19-Jan-2011 21:00
2	End Date & Time:	21-Jan-2011 15:00
3	Time Difference:	=B2-B1

Result:

	A	B
1	Start Date & Time:	19-Jan-2011 21:00
2	End Date & Time:	21-Jan-2011 15:00
3	Time Difference:	1.75

In the example above, the date and time in cell B1 is internally represented by the number, 40562.875 and the date and time in cell B2 is internally represented by the number 40564.625. Subtracting these two numbers gives the result 1.75. I.e. the time difference between the two dates and times is 1.75 days.

Setting Cells to Have the [h]:mm Format:

-	Right click on the cell to be formatted
-	Select the option Format Cells...
-	Ensure the Number tab is selected in the window that pops up
-	Select the option Custom from the list of Categories and type [h]:mm into the box on the right
-	Click OK

Cell B3 of the above results spreadsheet has the 'general' cell formatting. If you prefer to show the result as a number of hours and minutes, this can be done by setting cell B3 to have the time format [h]:mm, as shown in the spreadsheet below.

Result with [h]:mm Formatting:

	A	B
1	Start Date & Time:	19-Jan-2011 21:00
2	End Date & Time:	21-Jan-2011 15:00
3	Time Difference:	42:00

Note that the square brackets in the [h]:mm format definition tell Excel to display the total number of hours, even if this is greater than 24. If the square brackets were not included, Excel would break down the result into a date plus the remaining number of hours.

Return to the Excel Formulas Page

Return to the ExcelFunctions.net Home Page

Disclaimer

Privacy Policy