How to add or subtract hours in Microsoft Excel

Microsoft Excel Logo

If you are using Microsoft Excel for time tracking, you will probably need to get the final counts. You can add hours and minutes to see your total time spent or subtract start and end times to see time worked.

Add hours in Microsoft Excel

You can keep track of the time you spend working on a task or project. When that job is done, you’ll want the total time you spent. Whether it’s for your own records or for billing a client, you can use a combination of formulas and formatting to get accurate time totals.

Format cells for hours and minutes

Note that we will be using the H: MM structure to stay consistent with the timing of the hours and minutes. While Excel should recognize this when you enter your data, you can make sure of this by formatting your cells.

Select the cells containing your entries and right click and choose “Format Cells” or go to the Home tab, click “Format” in the ribbon and choose “Format Cells”.

On the Home tab, click Format, Format Cells

In the Format Cells window, make sure you are on the Number tab. Select “Custom” as the category. Then select “h: mm” from the list or enter the format in the Type box.

Select hour minute format

Click “OK” to apply the format to the cells.

Add hours using a formula

If you only have a few entries, you can enter a quick formula to add those hours and minutes.

Select the cell where you want your time to spend. Enter the following formula, replacing the cell references with your own.

=F2+F3

Hit enter to apply the formula and you should see your total hours and minutes for those entries.

Add the formula to add the times

Add hours using the Sum function

If your time tracking sheet has many entries, using the SUM function is the easiest solution.

Select the cell at the bottom of the entries. Go to the Home tab and click on “Sum” (formerly titled “AutoSum”).

On the Home tab, click Sum

Excel should recognize the cells you want to use. Otherwise, just drag the cells containing your times to fill in the formula and hit enter.

Select cells for formula

You should see the total number of hours and minutes for your entries.

Total hours and minutes

Format hours using 24 hours

When you add entries that use the 24 hour time, you may get an incorrect total. To avoid this, you can format the cell containing the result.

Select the cell containing the formula. Right click and choose “Format Cells”, or go to Home> Format> Format Cells.

In the Format Cells window, make sure the Number tab is selected. Choose “Custom” as the category and select “[h]: mm; @ “from the list or enter the format in the Type box.

Select extended hour-minute format

Click “OK” to apply the format to the cell. You should then see accurate results for entries using 24 hour hours.

Total hours and minutes using 24 hours

Subtract times in Microsoft Excel

Another convenient way to track time in Excel is to use start and end times. It could also apply to a project, or maybe a timesheet for your job. With these steps you can determine the number of hours worked.

Format cells for time

Like when adding hours and minutes, it’s best to make sure that the cells in your entries are formatted correctly. In this case, you will format them as a time, for example 4:30 a.m.

Select the cells containing your entries. Right click and choose “Format Cells” or go to Home> Format> Format Cells.

In the Format Cells window, on the Number tab, choose “Time” as the category. Select “13:30” for the hour and minute format.

Select the hour minute hour of day format

Click “OK” to apply the format to your cells.

Subtract your times

Select the cell where you want to get your result and enter the following formula, replacing the cell references with your own.

=C2-B2

Press Enter to apply the formula.

Then you may need to format the cell in hours and minutes to show the total time spent. By default, your results can be displayed as the time of day.

Enter the formula to subtract the times

Select the cell containing the formula, right click and choose “Format Cells” or go to Home> Format> Format Cells.

In the Format Cells window, on the Number tab, choose “Custom” as the category. Select “h: mm” or enter it in the Type box and click “OK”.

Select hour minute format

You should then see the correct number of hours and minutes for the time spent.

Correct format showing hours and minutes

If you’re working with dates in addition to hours, also check out our guide to adding or subtracting dates in Microsoft Excel!

Leave a Comment