Quickly find the difference between two times, control the format of the result, and extend the calculation to a list of times. For example, you can quickly subtract one time from another time, or calculate the duration of agenda items for a meeting.
Use the provided sample data and the following procedures to learn how to calculate times for a meeting agenda.
Copy the sample data
To better understand the Subtract times function, copy the following sample data to cell A1 on a blank worksheet.
-
Open a blank workbook or worksheet.
-
Select the following sample data, including the headings.
Agenda
Start time
End time
Duration
Introduction
8:45 AM
10:35 AM
Brainstorm
10:35 AM
3:30 PM
Summarize
3:30 PM
4:35 PM
Debrief
4:35 PM
5:00 PM
-
Press
-
In the worksheet, select empty cell A1, and then press
Note: Do not select the row or column headings in the blank sheet (1, 2, 3... A, B, C...) when you paste the sample data.
Pasting sample data in a blank sheet
Calculate the number of hours and minutes between two times
-
Select cell D2, which is the first blank cell in the Duration column if you copied the column headings to your worksheet along with the sample data.
-
On the Formulas tab, click Text > TEXT.
=TEXT() appears in the cell.
-
In the Formula bar, place your cursor inside the (), click value, and then type C2-B2.
-
Inside the (), click format_text, and then type "h:mm", including the quotation marks, to format the result as hours and minutes.
-
Press RETURN .
The result is 1:50 (1 hour, 50 minutes).
-
Select cell D2.
-
To copy the formula into the remaining rows, drag the fill handle
Excel adjusts the cell references automatically to include the correct values for each row.
Tips:
-
See TEXT function to learn more about this formula.
-
If you use both a format that is applied with the TEXT function and a number format, the TEXT function takes precedence.
-
Additional formula examples
To try the examples, copy the following table to any blank part of the sheet that contains the sample data, and then click the formula cells to see how the formula is constructed in the Formula Builder.
|
Formula |
Description (Result) |
|---|---|
|
=TEXT (C3-B3,"h") |
Hours between two times with the cell formatted as "h" (4) |
|
=TEXT (C3-B3,"h:mm") |
Hours and minutes between two times with the cell formatted as "h:mm" (4:55) |
|
=TEXT (C3-B3,"h:mm:ss") |
Hours, minutes, and seconds between two times with the cell formatted as "h:mm:ss" (4:55:00) |
|
=INT ((C3-B3)*24) |
Total hours between two times (4) |
|
= (C3-B3)*1440 |
Total minutes between two times (295) |
|
= (C3-B3)*86400 |
Total seconds between two times (17700) |
|
=HOUR (C3-B3) |
Hours between two times, when the difference does not exceed 24 (4) |
|
=MINUTE (C3-B3) |
Minutes between two times, when the difference does not exceed 60 (55) |
|
=SECOND (C3-B3) |
Seconds between two times, when the difference does not exceed 60 (0) |
