Wednesday, January 6, 2021

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 Subtract times function, copy the following sample data to cell A1 on a blank worksheet.

  1. Open a blank workbook or worksheet.

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

  3. Press COMMAND + C .

  4. In the worksheet, select empty cell A1, and then press COMMAND + 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

    Select sample data

Calculate the number of hours and minutes between two times

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

  2. On the Formulas tab, click Text > TEXT.

    On the Formulas tab, click text.

    =TEXT() appears in the cell.

  3. In the Formula bar, place your cursor inside the (), click value, and then type C2-B2.

  4. Inside the (), click format_text, and then type "h:mm", including the quotation marks, to format the result as hours and minutes.

  5. Press RETURN .

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

  6. Select cell D2.

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

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

    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

  3. Press COMMAND + C .

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