Mastering Time Calculations: A Step-by-Step Guide to Adding and Subtracting Dates in Microsoft Excel

Quick Links

• Add Times in Microsoft Excel
• Subtract Times in Microsoft Excel

If you use Microsoft Excel for time tracking, you’ll likely need to get final tallies. You can add hours and minutes to see your total time spent or subtract start and end times to see the amount of time worked.

Add Times in Microsoft Excel

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

Format the Cells for Hours and Minutes

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

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

In the Format Cells window, make sure that you’re on the Number tab. Select “Custom” as the Category. Then, select “h:mm” from the list or enter the format into the Type box.

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

Add Times Using a Formula

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

Select the cell where you want your time to go. 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 Times Using the Sum Function

If your time-tracking sheet will contain many entries, using the SUM function is the easiest way to go.

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

Excel should recognize the cells that you want to use. If not, simply drag through the cells containing your times to populate the formula and hit Enter.

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

Format Times Using 24 Hours

When you’re adding entries that use 24-hour time, it’s quite possible that you’ll get an incorrect total. To avoid this, you can format the cell containing the result.

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

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

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

Subtract Times in Microsoft Excel

Another handy way to track time in Excel is with start and end times. This 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 the Cells for Time

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

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

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

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

Subtract Your Times

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

=C2-B2

Hit Enter to apply the formula.

Next, you might need to format the cell for hours and minutes to show the total time spent. By default, your results might display as a time of day.

Select the cell containing the formula, right-click, and pick “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 into the Type box and click “OK.”

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

If you’re working with dates in addition to times, check out our how-to for adding or subtracting dates in Microsoft Excel, too!

Also read: