Calculate the difference between two times

Let's say you want find out how long it takes for an employee to complete an assembly line operation or a fast food order to be processed at peak hours. There are several ways to calculate the difference between two times.What do you want to do?

Present the result in the standard time format (hours : minutes : seconds)

Present the result in a total based on one time unit (120 minutes)

Present the result in the standard time format (hours : minutes : seconds )

There are two approaches that you can take to present the results in the standard time format. You can use the subtraction operator (-) to find the difference between times and:

A custom format code in the cell.
The TEXT function to format the times.

When you use the time format codes, hours never exceed 24, minutes never exceed 60, and seconds never exceed 60.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

A	B
Start time	End time
6/9/2007 10:35 AM	6/9/2007 3:30 PM
Formula	Description (Result)
=B2-A2	Hours between two times with the cell formatted as "h" (4)
=B2-A2	Hours and minutes between two times with the cell formatted as "h:mm" (4:55)
=B2-A2	Hours, minutes, and seconds between two times with the cell formatted as "h:mm:ss" (4:55:00)
=TEXT(B2-A2,"h")	Hours between two times (4)
=TEXT(B2-A2,"h:mm")	Hours and minutes between two times (4:55)
=TEXT(B2-A2,"h:mm:ss")	Hours, minutes, and seconds between two times (4:55:00)

The previous example shows two ways to calculate the difference between two times. You can use:

A simple subtraction formula and the result formatted with a custom code. Select the cell, and on the Home tab, in the Number group, click the arrow next to the Number Format box, click More Number Formats, click Custom in the Category list, and then click a custom format in the Type box.
The TEXT function which has a formatting argument.

Note If you use both a format applied with the TEXT function and number format, the TEXT function takes precedence.

Function and format details

TEXT

Display numbers as dates or times

Present the result in a total based on one time unit (120 minutes)

To do this task, use the INT function, or HOUR, MINUTE, and SECOND functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

A	B
Start time	End time
6/9/2007 10:35 AM	6/10/2007 3:30 PM
Formula	Description (Result)
=INT((B2-A2)*24)	Total hours between two times (28)
=(B2-A2)*1440	Total minutes between two times (1735)
=(B2-A2)*86400	Total seconds between two times (104100)
=HOUR(B2-A2)	Hours between two times, when the difference does not exceed 24. (4)
=MINUTE(B2-A2)	Minutes between two times, when the difference does not exceed 60. (55)
=SECOND(B2-A2)	Seconds between two times, when the difference does not exceed 60. (0)

Function details