Calculate the difference between two times

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

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.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
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 then format the result by selecting the Custom category and entering the appropriate format code in the Type box under the Number tab of the Format Cells dialog 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

Number format codes

Display numbers as dates or times

Top of Page Top of Page


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.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
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

INT

HOUR

MINUTE

SECOND

Top of Page Top of Page

 
 
Applies to:
Excel 2003