Subtract time

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.