Video: Subtract time

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

You can subtract time in Excel almost like you subtract other types of numbers. To do this, you use a formula; there isn’t any auto-subtract button. Like with adding time, you can also subtract a unit of time, such as 1½ hours, from a time of day, such as 10:00 a.m., by using the TIME function.

Subtract time

You know your start and end times at a volunteer project, and want to know the elapsed time.

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

  2. In cell C2, type the end time, including “a” or “p” as appropriate, and 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 pressing Enter.
    We need to format cell D2 to get rid of “AM.”

  5. Then on the HOME tab, click Format > Format Cells.

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

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

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

Want more?

Add or subtract time

Create or delete a custom number format

TIME function

You subtract time in Excel almost like you subtract other types of numbers.

The one exception is that Excel doesn't support a negative number formatted as time.

Excel displays negative time as an endless series of number signs.

In this example, we want to figure out how many hours a person worked, and we want the results displayed as a number with a fraction of an hour, such as 6.50 hours, as opposed to 6:30, 6 hours and 30 minutes.

Time is stored in Excel as a fraction of a 24-hour day. So, 12 p.m. would be .5, half a day.

To go from half a day to hours, we need to multiply it by 24 hours in a day.

Enough said. Let's subtract time. To do this, you use a formula. There isn't an auto-subtract button.

Type an = sign, open parenthesis, click cell C2, type a - sign, click cell B2, type a closing parenthesis, asterisk (which multiplies numbers), 24 (for 24 hours in a day).

The parentheses ensure that the subtraction occurs first, and the resulting number is, then, multiplied by 24, and press Enter.

And we see that Richard worked 6.5 hours.

I want two digits after the decimal, so I right-click the cell, and click Format Cells.

Under Category, I click Number. The default is two digits, so I click OK.

To fill in the rest of the cells, you don't have to type the formula multiple times.

Click the cell with the formula, put the mouse pointer over the bottom right-hand corner until it becomes a black plus sign, click and hold the left mouse button, and drag the plus sign over the cells you want to fill.

The formula and formatting are copied into the cells.

In this example, the elapsed time is greater than 24 hours, and I want it shown as time, not a number.

I type an = sign, click cell C7, type a - sign, click cell B7, and press Enter.

To format the cell to show the results the way I want it, I right-click the cell, and click Format Cells.

Under Categories, I click Custom.

In the Type list, I scroll down to the square bracket format that we created in the Add Time video, and click OK.

The elapsed time is 31 hours and 30 minutes.

You can also subtract a unit of time, such as one and a half hours, from a time of day, such as 10:00 a.m., by using the TIME function.

I type an = sign, click A10, type a - sign, TIME, left parenthesis, 1 (for the number of hours we want to subtract), comma, 30 (for the minutes we want to subtract), comma, 0 (for the seconds), a closing parenthesis, and press Enter.

And we have our calculated time of 8:30 a.m.

Now you have a pretty good idea about how to add and subtract time. Of course, there's always more to learn.

So check out the course summary at the end, and best of all, explore Excel 2013 on your own.

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×