Sum Time in Excel

Excel Sum Time (Table of Contents)

Suppose you have been provided with two-time values and want to subtract or add the time in excel, so the question is how this will work? Does it require anything extra out of the box? Or this is the same thing we do with numbers.
Here the answer of above questions are, this subtraction and addition of time in excel will work the same as it is working with numbers.
The difference between these time addition and the addition of the normal number is the format of numbers for that particular cell and mostly even that doesn’t matter at all.
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 a benefit of getting the results in the required format.

Let’s understand how to sum time in Excel by using a few examples.

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

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

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 the total time he spent is 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 the justified/realistic result,

You can see from the above screenshots that when we have added times 16:42 and 20:12 the results were supposed to be different than 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, then click the Cells group and then choose the Format and format cells after that.

Method: 2

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

After that, the steps are the same for both the 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 below screenshot for reference.

Now as we have understood the basics, let’s move to some other useful feature excel brings to these time calculations.
In this example suppose we have a data of students for which they 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 format of the cell as we have done before which is shown in the below screenshot.

So select the cells than right-clicking with the mouse and select format cells… and then click custom and type [hh] under Type section.

After applying the same, we should have the result cell as per the screenshot below, 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 there are some candidates who took 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 these both laps in seconds.

As we discussed before that these time calculation mostly depends on the format you choose for your result cell, like if you are calculating the time for a race then you should choose the second format to get the time in seconds. And if we want to calculate the working hours of the employees 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 us =sum or =substitute
The time calculation is mostly dependent on how you format the result cell.