Updated August 9, 2023

Sum Time in Excel

Suppose you have been provided with two-time values and want to subtract or add the time in Excel so the question is, how will this work? Does it require anything extra out of the box? Or this is the same thing we do with numbers.
The answer to the above questions is that this subtraction and addition of time in Excel will work the same as with numbers.
The difference between these time addition and the addition of the normal number is the format of numbers for that particular cell; mostly, even that doesn’t matter.
We will see in the following examples that the format of the result of an addition/subtraction makes it very useful to do this thing in Excel.
Here we need to understand that doing this calculation in Excel has the benefit of getting the results in the required format.

Let’s use a few examples to sum time in Excel.

You can download this Sum Time Excel Template here – Sum Time Excel Template

As per the above screenshot, suppose we have the employee’s in and out time data.
We want to calculate their working hours by subtracting the out time from time; as you can see, we have applied the simple subtraction formula in the cell.
After applying the formula, we will get a result shown in the screenshot below.

After that, we will apply the same formula to other cells by dragging the D2 cell.

In this example, the time is formatted as HH: MM format as it’s most widely used. Also, it’s in 24 Hours format by default; otherwise, we can format it by indicating AM / PM in 12 Hours format.
From the screenshot, we can see that Ritesh’s in time is 08:55 and out time is 18:01, so he spent 9 hours and 5 minutes that day in the office.
So that’s the most basic use of time subtraction/addition.

Here let’s see an example where you have to change the format of the cell to get a justified/realistic result,

You can see from the above screenshots that when we added times 16:42 and 20:12, the results differed from what we got here.
So, why does that happen? This happened because the format of the time is in 24 hours by default.
Now to get the actual results in hours, we have to change the format of the result cell.
To change the format of the cell, there are two ways to do so as follows.

Method: 1

Go to the Home tab, click the Cells group, and then choose the Format and format cells afterward.

Method: 2

Right-click on the cell, then click on format cells.

After that, the steps are the same for both methods; click Custom in the category.
Now type our required format of cell – [h]: mm;@ and then click ok.
Do remember that we have to type colon after [h] and a semicolon after mm
Look into the below screenshot for your reference.

So after applying this format to our result cell, we shall have our supposed result. Find the screenshot for reference below.

Now that we have understood the basics, let’s move to some useful features Excel brings to these time calculations.
In this example, suppose we have data of students who took the time to complete two laps in a race organized by the school as follows.

So now here we have the data which can decide the winner undoubtedly, but we can make this data interesting by changing the format of the result cell.
At present, the cell is in hh:mm: ss.
Now suppose here we want to see only hours in the result cell; we have to follow the same procedure to change the cell format as we have done before, shown in the screenshot below.

So select the cells, right-click with the mouse, select format cells, click Custom, and type [hh] under the Type section.

After applying the same, we should have the result cell as per the screenshot below, and we can see that each runner has taken 2 hours, so this doesn’t turn out valuable for this data.

Now, if instead of hours, we want the result in minutes and seconds, so while changing in format instead of [hh], we will make the custom format [mm] for minutes and [ss] for seconds, respectively, then we should have data for minutes and seconds as below.

The above screenshot shows the time taken by a particular runner in minutes, which narrows the winning candidate, so even if some candidates took the same minutes, we need more accurate data than the minutes. So we should take seconds.
As per the below screenshot, we can see the time taken by the runner to complete both laps in seconds.

As we discussed, these time calculations mostly depend on the format you choose for your result cell; if you are calculating the time for a race, you should choose the second format to get the time in seconds. And if we want to calculate the employees’ working hours, we should choose the hour format.
So we should use a suitable format for these calculations
As we have seen in the above examples, we can use this calculation with the general logic of subtraction and addition, but in the above examples, we have mostly taken 2 or 3 subjects, but when we would have more subs, we can use =sum or =substitute
The time calculation is mostly dependent on how you format the result cell.