Add or subtract time

Suppose you want to add two different times together to get a total, or subtract one time from another to get the total time spent working on a project. Fortunately, Excel can easily add or subtract time, using simple formulas.

Add time

Suppose that you want to know how many hours and minutes it will take to complete two tasks. You estimate that the first task will take 6 hours and 45 minutes and the second task will take 9 hours and 30 minutes.

1. In cell B2 type 6:45, and in cell B3 type 9:30.

2. Type =B2+B3 in cell B4, and then press Enter.

   

   The total of 16 hours and 15 minutes is returned for the completion the two tasks.

   Tip:  You can also add up times by using the AutoSum function to sum numbers. Select cell B4, and then on the Home tab, choose AutoSum. The formula will look like this: =SUM(B2:B3). Press Enter to get the result: 16 hours and 15 minutes.

Well, that was easy enough, but there's an extra step if your hours add up to more than 24. You need to apply a special format to the formula result.

Add up more than 24 hours

1. In cell B2 type 12:45, and in cell B3 type 15:30.

2. Type =B2+B3 in cell B4, and then press Enter.

   

   The result is 4:15, which is not what you would expect.

3. To display the time as more than 24 hours, select cell B4.

4. On the Home tab, in the Cells group, choose Format, and then choose Format Cells.

   

5. In the Format Cells box, choose Custom in the Category list.

6. In the Type box, at the top of the list of formats, type [h]:mm;@and then choose OK.

   Note the colon after [h] and a semicolon after mm.

   The result is 28 hours and 15 minutes. The format will be in the Type list the next time you need it.

Subtract time

Say that you and your friends know your start and end times at a volunteer project, and want to know how much time you spent. In other words, you want the elapsed time, which is the difference between two times.

1. In cell B2, type the start time, enter a space, type "a" for AM or "p" for PM, and then press Enter.

2. In cell C2, type the end time, including "a" or "p" as appropriate, and then press Enter.

3. Type the other start and end times for your friends, Joy and Leslie.

4. In cell D2, subtract the end time from the start time by typing the formula =C2-B2, and then press Enter.

   

5. In the Format Cells box, click Custom in the Category list.

6. In the Type list, click h:mm (for hours and minutes), and then click OK.

   Now we see that Richard worked 3 hours and 45 minutes.

   

7. To get the results for Joy and Leslie, copy the formula by selecting cell D2 and dragging to cell D4.

   The formatting in cell D2 is copied along with the formula.

Subtract time that's more than 24 hours

To subtract the difference between two times that total more than 24 hours, create a special format.

1. Referring to the above example, select cell B1 and drag to cell B2 so that you can apply the format to both cells at the same time.

2. In the Format Cells box, click Custom in the Category list.

3. In the Type box, at the top of the list of formats, type m/d/yyyy h:mm AM/PM.

   Notice the empty space at the end of yyyy and at the end of mm.

   The new format will be available when you need it in the Type list.

4. In cell B1, type the start date, including month/day/year and time using either "a" or "p" for AM and PM.

5. In cell B2, do the same for the end date.

6. In cell B3, type the formula =(B2-B1)*24.

   The result is 31.5 hours.