Tuesday, February 28, 2017

Subtract times

Subtract times

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 steps, copy the following sample data to cell A1 on a blank sheet.

  1. Create a blank workbook or sheet.

  2. Select the following sample data.

    Note: Do not select the row or column headings (1, 2, 3...  A, B, C...) when you copy the sample data to a blank sheet.

    Selecting sample data in Help

    Select sample data

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

  1. Press COMMAND + C .

  2. In the sheet, select cell A1, and then press COMMAND + V .

Calculate the number of hours and minutes between two times

  1. Select cell D2, which is the first blank cell in the Duration column.

  2. On the Formulas tab, under Function, click Formula Builder.

    Formulas tab, Function group

    An equal sign (=) appears in the cell.

  3. In the cell, type TEXT.

  4. In the list that appears, click TEXT.

    The argument fields for the TEXT function appear at the bottom of the Formula Builder.

  5. Under Arguments, click the box next to value, and then type C2-B2.

  6. 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.

  7. Press RETURN .

    The result is 1:50 (1 hour, 50 minutes).

  8. Select cell D2.

  9. To copy the formula into the remaining rows, drag the fill handle   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)

See also

Subtract dates

Show dates as days of the week

Insert and format the current date and time

No comments:

Post a Comment