Add or subtract time

Not many people can easily 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.

Time for two tasks added up

It will take 16 hours and 15 minutes to complete the two tasks.

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

Add up more than 24 hours

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.

  1. For example, in cell B2 type 12:45, and in cell B3 type 15:30.
  2. Type =B2+B3 in cell B4, and then press Enter.

Time added up that's over 24 hours total an unexepcted result of 4:15

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

  1. To display the time as more than 24 hours, click in cell B4
  2. Then on the Home tab, click Format > Format Cells.

On the Home tab, Format button, and the Format cells button on the menu

  1. In the Format Cells box, click Custom in the Category list.
  2. In the Type box, at the top of the list of formats, type [h]:mm;@ and then click OK. (That’s a 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, or the difference between two times.

  1. In cell B2 type the start time, enter a space, and then type “a” for AM or “p” for PM, and press Enter. In cell C2, type the end time, including “a” or “p” as appropriate, and press Enter. Type the other start and end times for your friends Joy and Leslie.
  2. In cell D2, subtract the end time from the start time by typing the formula: =C2-B2, and then pressing Enter.

One time subtracted from another to get elapsed time

We need to format cell D2 to get rid of “AM.”

  1. Follow step 4 under Add up more than 24 hours.
  2. In the Format Cells box, click Custom in the Category list.
  3. 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.

Elapased times listed in column D

  1. To get the results for Joy and Leslie, copy the formula by clicking in cell D2 and dragging to cell D4. The formatting in cell D2 is copied along with the formula.

Subtract time that’s over 24 hours

To subtract the difference between two times over 24 hours, create a special format.

Elapsed time over 24 hours

  1. In the above example, click in cell B1 and drag to cell B2 so that you can apply the format to both cells at the same time.
  2. Follow step 4 under Add up more than 24 hours.
  3. In the Format Cells box, click Custom in the Category list.
  4. 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.

  1. In cell B1 type the start date, including month/day/year and time using either “a” or “p” for AM and PM.
  2. Do the same for the end date in cell B2.
  3. In cell B3, type this formula: =(B2-B1)*24. The result is 31.5 hours.
 
 
Applies to:
Excel 2013