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 Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
| 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
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 Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
|
|
| 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
United States
Email this link
Printer-Friendly Version
Share
Hide All
