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
+ C . -
In the worksheet, select empty cell A1, and then press
+ V .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) |
Copy the sample data
To better understand the steps, copy the following sample data to cell A1 on a blank sheet.
-
Create a blank workbook or sheet.
-
Select the following sample data.
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 in the blank sheet.
Pasting sample data in a blank sheet
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
+ C . -
In the sheet, select cell A1, and then press
+ V .
Calculate the number of hours and minutes between two times
-
Select cell D2, which is the first blank cell in the Duration column.
-
On the Formulas tab, under Function, click Formula Builder.
An equal sign (=) appears in the cell.
-
In the cell, type TEXT.
-
In the list that appears, click TEXT.
The argument fields for the TEXT function appear at the bottom of the Formula Builder.
-
Under Arguments, click the box next to value, and then type C2-B2.
-
Under Arguments, click the box next to 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) |
No comments:
Post a Comment