Calculate the difference between two times

Let's say that 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.

Present the result in the standard time format

There are two approaches that you can take to present the results in the standard time format (hours : minutes : seconds). You use the subtraction operator (-) to find the difference between times, and then do either of the following:

Apply a custom format code to the cell by doing the following:

Select the cell.

On the Home tab, in the Number group, click the arrow next to the General box, and then click More Number Formats.

In the Format Cells dialog box, click Custom in the Category list, and then select a custom format in the Type box.

Use 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 Table 1 — Present the result in the standard time format

Copy the following table to a blank worksheet, and then modify if necessary.

1

2

3

4

5

6

7

8

9

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 (4). You must manually apply the custom format "h" to the cell.

=B2-A2

Hours and minutes between two times (4:55). You must manually apply the custom format "h:mm" to the cell.

=B2-A2

Hours, minutes, and seconds between two times (4:55:00). You must manually apply the custom format "h:mm:ss" to the cell.

=TEXT(B2-A2,"h")

Hours between two times with the cell formatted as "h" by using the TEXT function (4).

=TEXT(B2-A2,"h:mm")

Hours and minutes between two times with the cell formatted as "h:mm" by using the TEXT function (4:55).

=TEXT(B2-A2,"h:mm:ss")

Hours, minutes, and seconds between two times with the cell formatted as "h:mm:ss" by using the TEXT function (4:55:00).

Note: If you use both a format applied with the TEXT function and apply a number format to the cell, the TEXT function takes precedence over the cell formatting.