Often may come a time, when you are trying to figure out what time it will be after certain hours. You can also want to check how many hours you have spent on a project task. Or you are just trying to figure out how to add two different time values together to figure out when your project would be completed.
Manually trying to sum time values together would be a time-consuming and cumbersome process. But with excel, it’s a breeze. But you do have to tread carefully to use the correct formatting.
You also have to be careful about summing up two time values when they go over 24 hours. The most notorious problem faced while trying to sum two time values that exceed 24 hours. But fear not. We also got a solution for that in this guide too.
Let’s get to it.
Contents
5 Methods to Sum Time in Excel
If you want to sum time in Excel, there are several methods to do so. Some require practically no knowledge of formulas, while others require you to write simple formulas. But while working with time in Excel you have to pay special attention to the cell formatting to get an accurate result.
For this guide, we have gathered 5 methods and a bonus tip on how to sum time in Excel.
Let’s dive in.
Traditional Method: The Boring Method
The first method is the easiest method to sum time in your excel document. Say you have the data of how many hours your project members have worked each day. And you want to add the hours to know how many hours in total they have worked. With this simple method, you can do exactly that.
For example, we have this sample dataset containing some names and the data on how many hours they worked on Sunday and Monday. And we want to know how many hours they have worked in total.
The process is simple. So just follow along:
In the formula bar of an adjacent cell place an equal sign and enter the two cell references you want to add divided by a plus sign.
For our example, in the D4 cell, we will enter
=B4+C4
As you can see in the image, this simply returns the total number of hours Robert has worked in those two days.
To know how many hours the other members have worked, just use the autofill handler to copy the formula below as shown in the image.
As you can see, the individual working hours for each of the members are shown in the adjacent cells.
But, this method has a drawback. If you have anything other than a time value in your cell, the function will return a #VALUE! Error.
With SUM: Quick and Easy
Although the method shown above is a quick one, you may face issues with the method like the #VALUE! error we showed you earlier. To avoid issues while summing time, you can use the SUM function to add the working hours.
The SUM function works similar to the previous method, but is more versatile to work with. To use the SUM function to sum time in Excel, you can just follow this guide:
First, similar to the previous method, in an adjacent cell place the SUM formula. In our case the formula looks like this:
=SUM(B4,C4)
As shown in the picture, you get the total working hours of Robert in the cell D4.
The SUM function is more versatile because it can even work with a range of cell references. So you can also write the formula like this:
=SUM(B4:C4)
As we can see, you get the sum of all the time values easily.
And similar to the previous method, if you want, you can use the autofill handler to copy the formula below as you need to.
Similar to the previous method, as shown in the picture, you can get the sum of the time in the subsequent cells by just copying the formula to the subsequent cells.
Using AutoSUM: Let Excel Do the Hard Work
Another function that can achieve the same task of summing time in Excel is the AUTOSUM function. The AutoSUM function practically does the same thing as the SUM function do. But it is one of Excel’s smart feature that automatically writes the SUM formula for you.
To use the AutoSUM feature to sum time, you have to click on the AutoSUM icon. The AutoSUM icon lives on the far right hand corner on the home tab.
So, first click on the adjacent tab where you want the sum of the time to be and click on the AutoSUM icon as we have showed in the image below.
Immediately after clicking on the icon Excel will present you with the suggestion to sum the time on the previous cells.
From there, all you have to do is just press Enter and you are done. If you need the total time values of the subsequent cells, you can click and drag to select the empty cells where you want your total time to be.
And then, as we showed in the image, just click on the AutoSUM option. Alternatively, you can just use the Autofill handler to copy the formula ,to the subsequent cells as we showed you in the previous methods.
You will see that the total working hours for the members are shown in the cells.
And unlike the previous method, the SUM function ignores texts and will not return a #VALUE! Error if there are any texts in your time value data.
Using The TIME Function: Be Efficient
The previous methods we showed you used basic Excel adding techniques. But if you are eager to be efficient a better way to sum time is to use the TIME function built in Excel.
The TIME function gives you finetuned control over the time you add in Excel. You can add hours, minutes, and seconds specifically. So, say you want to add 6 hours and 10 minutes and 20 seconds to your existing work hours.
In our case we typed in the formula:
=TIME(HOUR(B4)+6,MINUTE(B4)+10,SECOND(B4)+20)
And press enter.
Formula Breakdown
In this formula, the TIME function takes the time written in the B4 cell and divides it into hours, minutes and seconds. So, the formula works like this:
SECOND(B4)+20 Takes the second’s value in the B4 cell and adds 20 seconds.
MINUTE(B4)+10 Takes the minutes’ value in the B4 cell and adds 10 minutes.
HOUR(B4)+10 Takes the hour’s value in the B4 cell and adds 6 hours.
Then, the TIME function returns the value with the added hours, minutes, and seconds. Although the formula looks complex, if you know how it works, it’s very easy.
Now, similar to the previous methods, just copy the formula to the subsequent cell with the use of the Autofill handle.
Bonus
Now for a bonus, we will show you another interesting way you can use the TIME function to sum time in your Excel. In the previous method, we used the TIME Function to add specific hours, minutes and seconds to our time.
But we can do the same thing with a shorter formula with TIME. To do this, with our previous example, we will write the formula:
=B4+TIME(6,10,20)
Interesting, right? But as you can see, the formula works the same way as the previous method. But this one is a much shorter formula. So this will be easier for you to practice.
But, all of these methods that we can use to sum time have one catch, You will surely stumble upon this if you are working with time values. That is, if you want to sum time in Excel that exceeds 24 hours, Excel will show you the wrong total hours.
For example, say we want to add 20 hours to Robert’s working hours for Sunday. So our formula will be:
=D4+(20,0,0)
But we know for sure, that 8.30 hours and 20 hours do not add up to only 4 hours and 30 minutes. Excel shows this because of its formatting. To fix this, press CTRL+1 to bring up the formatting menu.
And, under the numbers tab click on custom and choose the [h]:mm:ss formatting. And now you will see the proper formatting for the total working hours.
This same trick works for summing minutes and seconds to your time value. So, if you want to add 20 minutes to the B4 cell’s value, in the formula bar type in:
=B4+TIME(0,20,0)
As you can see, the short formula adds 20 minutes to your desired time from the cell B4. Similarly, you can add seconds to your time as well.
Summing Time: An Even Shorter Formula
For summing hours that will span beyond 24 hours, you can also use a shorter formula. For our case, the formula is
=B4+(20/24)
This formula practically returns the same answer, but it is more specific to a sum of hours more than 24 hours. All this formula does is divides the final time by 24 hours so that you get a total hour count.
Similar to all the other methods above, just use the autofill handler to copy the formula to the cells below if you want to add 20 hours to the entire range of time values.
This can be similarly done for minutes and seconds as well. If you want to add minutes to your time and you know tha the sum will exceed 60 minutes a.k.a. 1 hour, then you can use a similar formula.
For example, suppose we want to add 40 more minutes to Robert’s working hours. The formula for our case will be:
=B4+(40/1440)
Here we are dividing the value we want to add by 1440 because it is the equivalent of 24 hours.
Easy enough? Now, let’s check out the formula to add seconds to your seconds’ value (that will exceed 60 seconds). Again, as an example, we are going to add 90 seconds to our time. The formula will be:
=B4+(90/86400)
As you can see, the formula added 1 minute and 30 seconds or 90 seconds to our time.
Similar to what we did with minutes, we are dividing the seconds by 86400 which equates to 24 hours. So, this formula adds time in terms of 24 hours.
Formatting! Formatting! Formatting!
Although we have shown you how to sum time in Excel, you would notice that we needed to format the cells for the specific time format. As excel stores date as serials, it also stores time as fractions of those serial numbers.
So whenever you are working with time in Excel, make sure to format your cell to your required format to ensure accurate results.
If you want to see only the total number of hours after you have summed up a time, press CTRL+1 and under the Numbers tab click on Custom and choose [hh]
This will only show you the hours of total working hours ignoring the minutes and seconds.
Similarly, if you want to see the minutes choose [mm] in the Custom option and Excel will show you only minutes.
The same goes for the seconds as well. Just select the [ss] option in the format menu.
Wrapping up
In this guide we have looked at various methods to sum up time in Excel. As we saw, the primary thing to look out for is the formatting of the cell. Otherwise, the basic function is similar to summing numbers.
Although you could use the traditional method to sum time, we would strongly suggest that you use the TIME function to do so. Because the TIME function is much more efficient and less time consuming. But if you keep practicing you will be efficient in all of the methods we showed you in this tutorial.
So, what are you waiting for? Go out there and figure out that ending date of your project.
Similar Post: