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

   

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 + C .

2. In the sheet, select cell A1, and then press + 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.

   

   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   .

   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