How to Sum Time in Excel (9 Suitable Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will demonstrate to you how to sum time in Excel. While working in Excel, we may need to add time. To clarify the process of summing time we will go over several methods to sum time in Excel.

1. Using Excel Formula to Sum Time in Excel

The conventional approach is the simplest way to add time. To describe this method, we will use the following dataset. The dataset gives us the working hours of six staff for Thursday and Friday. So, in this example, we want to sum the working hours of each staff member for the particular days. Let’s see how we can do this.

Firstly, select cell E6 and insert the following formula:

=C6+D6

Next, press Enter. We can see the total working hours for John on Thursday and Friday in cell E6.

After that, insert the corresponding formulas for other staff from the cell (E7:E10).

Finally, by pressing Enter, we will get the summation of working hours.

2. Using The SUM Function to Sum Time in Excel

Another approach to add time in Excel is using the SUM formula. Like the previous example, we will use the same dataset for this example too.

First, select cell E6.
Next, Insert the following formula:

=SUM(C6:D6)

Then, press ENTER. We will get the value of the total working hours on Thursday and Friday for John.

Finally, insert the following formulas for the corresponding cells. Then press ENTER. We will get the total working hours for all the staff.

We can use the Fill Handle tool instead of inserting formulas for individual staff. To use this, just do the following steps:

Select cell E10. Enter the total working hours for John in that cell by using the following SUM formula:

=SUM(C6:D6)

Next, select the Fill Handle at the bottom right corner of that cell.
Finally, drag the Fill Handle to cell E10. We will get the values of total working hours for all the staff.

3. Applying The AutoSum Option to Add Time in Excel

AutoSum is a very fast and convenient way to add time in Excel. In this method, we will use the same dataset that was used in the previous example. To add time using AutoSum, we will follow some steps:

In the beginning, select cell range E6:E10.

Then, go to the AutoSum option from the ribbon. From the drop-down select the Sum option.

Finally, we can see all the added values of working hours in Column E.

4. Using Excel TIME Function to Sum Time in Excel

Using the TIME Function is very effective if you want to add a specific time. Suppose we have a dataset like the previous one. But here we have only one column of the working hours. We want to add 2 hours 10 minutes and 50 seconds with each of the staff’s working hours. Let’s see how can we do this:

First, select cell D6. Insert the following formula in that cell:

=TIME(HOUR(C6)+2,MINUTE(C6)+10,SECOND(C6)+50)

Next, press ENTER. We can see the value of cell C6 in cell D6 after adding 2 hours 10 minutes and 50 seconds.

Finally, drag the Fill Handle tool to cell D10. By doing this we have added 2 hours 10 minutes and 50 seconds to the working hours of all the staff.

🔎 How Does the Formula Work?

SECOND(C6)+50: The SECOND function adds 60 seconds with cell C6.
MINUTE(C6)+10: The MINUTE function adds 10 minutes with cell C6.
HOUR(C6)+2: The HOUR function adds 2 hours with cell C6.
TIME(HOUR(C6)+2,MINUTE(C6)+10,SECOND(C6)+50): Gives the new time value after adding all the parameters in return.

Similar Readings

5. Applying The TEXT Function to Sum up Time in Excel

We can use the TEXT function to sum time in Excel also. It uses the text string of the cells to add the values of two cells. In this case, we have a dataset of 5 runners while completing 2 laps. We will calculate the total time for 2 laps in 3 different formats ‘h’, ‘h: mm’, and ‘h:mm:ss’. Let’s see the example step by step:

Select cell E5. Insert the following formula:

=TEXT(C5+D5,"h")

Next, press ENTER. We will get the total value in cell E5 in an hour format.

Here h converts the added value in an hour format. The C5+D5 part adds cells C5 & D5. The TEXT function provides the added value.

Then, select cell F5. Insert the following formula:

=TEXT(C5+D5,"h:mm")

Next, press ENTER. We will get the total value in cell F5 in an hour format.

The h:mm part converts the added value into hours and minutes. C5+D5 add cells C5 & D5.In the end, the TEXT function gives the summation in “h:mm” format.

Again, select cell G5. Insert the following formula:

=TEXT(C5+D5,"h:mm:ss")

After that, press ENTER. We will get the total value in cell G5 in an hour format.

Here h:mm:ss transform the added value in hours, minutes, and seconds format. C5+D5 add cells C5 & D5. The TEXT function returns the added value.

Then, select cell range E5:G5.

Finally, drag the Fill Handle tool. We will get the corresponding added values of 2 laps for all the runners in all three formats.

6. Adding up Hours in Excel

This section will discuss how to add hours in Excel. We can do this in the following two ways:

6.1. Adding up Under 24 Hours

Here, we have a dataset of working hours of 5 staff. We will add 2 hours to each staff’s working hours. Let’s see how we can do this.

Firstly, select cell D6. Inset the following formula:

=C6+TIME(2,0,0)

Next, press Enter. We will get a new value of cell C6 in D6. The value of D6 is 2 hours more than C6.

Finally, drag the Fill Handle tool to cell D10. By doing this we will be able to add 2 hours with each value of working hours.

In this case, the formula =C6+TIME(2,0,0) adds 2 hours with cell C6.

6.2. Adding up Hours Over 24 Hours

Now we will head over to add more than 24 hours with a particular time value. We will continue with our previous dataset. In this case, we will add 28 hours with each value of our dataset.

Here,

Select cell D6 at first. Insert the following formula:

=C6+(28/24)

Then press ENTER. The return value is 11:30:00. This is not what we expected. To solve this we need to take the following steps.

Select cell D6.
Next, do right-click on the cell. From the available options select Format Cells.

Then, we can see the Format Cells box. From the box, select Custom as a category.
After that, from the type option select the format “[h]:mm:ss“. Press OK.

Finally, we can see the value in cell D6 is 28 hours more than in cell C6. If we drag the Fill Handle to cell D10 then we will get all the values of working hours more than 28 hours.

Here, the formula C6+(28/14) adds 28 hours with cell C6.

7. Calculating Total Minutes in Excel

Now, we will discuss the two ways to add minutes in Excel. We will use the same dataset for this that we used before.

7.1. Adding Minutes Under 60 Minutes

In this section, we will add less than 60 minutes with a particular value. We will add 25 minutes with the values of the dataset. Let’s see how we can do this.

Firstly, select cell D6. Insert the following formula:

=C6+TIME(0,25,0)

Then, press ENTER. We will get the value of cell C6 increased by 25 minutes in cell D6.

Finally, drag the Fill Handle tool to cell D10. We will get all the values of working hours increased by 25 minutes.

The formula =C6+TIME(0,25,0) increases the cell value by 25 minutes.

7.2. Adding Minutes Over 60 Minutes

On the other hand, if we want to add more than 60 minutes with a value, we have to go through a different process. We will continue with the same data that we used in the previous example.

Let’s see how can we do this:

In the beginning, select cell D6 and insert the following formula:

=C6+(80/1440)

Now, press ENTER.

Finally, drag the Fill Handle tool to cell D10.

In this dataset, the formula C6+(80/1440) adds 80 seconds to cell C6.

8. Adding Seconds to Time

Similar to the previous examples we also can add seconds with time values. We will solve this problem in two cases. One adds under 60 seconds and the other adds over 60 seconds.

8.1. Adding Seconds Under 60 Seconds

To add under 60 seconds we will continue with our previous dataset. Let’s see how can we do this:

Firstly, select cell C6. Insert the following formula:

=C6+TIME(0,0,50)

Next, press Enter. We will get a new value in cell D6, 50 seconds more than the value of cell D6.

In the end, drag the Fill Handle tool to cell D10. We are adding 50 seconds with all the values of column C6:C10.

With the C6+TIME(0,0,50) formula, we add 50 seconds to cell C6.

8.2. Adding Seconds Over 60 Seconds

If we want to add more than 60 seconds we have to take a different approach. In this method, we will use our previous dataset. Here we will add 90 seconds with all the values from cell C6 to cell C10. Let’s see the steps to do this:

Firstly, select cell D6. Insert the following formula:

=C6+(90/86400)

Next, press ENTER.

Finally, Drag the Fill Handle tool to cell C10. We will get the values of the cell (C6:C10) increased by 30 seconds in cell (D6:D10).

The formula C6+(90/86400) increase the value of cell C6 by 90 seconds

9. Summing Time and Formatting as Hours, Minutes, and Seconds in Excel

In this section, we will change the formats of our time values. We will convert time into three formats. These are:

Hours Format. [hh]
Minutes Format. [mm]
Seconds Format [ss]

9.1. Hours Format [hh]

We have the following dataset of total times. We want to convert the Total column to only the [hh] format.

At first, select cell (E6:E10).

Next, right-click and select the option Format Cells.

Now, we can see the Format Cells box. From the box select Custom as a category.
After that, from the type option select the format “[h]”. Then press OK.

Finally, we can see all the time values in [hh] format.

9.2. Minutes Format [mm]

To convert into minutes format we will continue with our previous dataset. We will convert the values of column (E6:E10) in minutes format. Let’s see how can we do this:

In the beginning, select cell (E6:E10).

Next, right-click and select the option Format Cells.

Then, we can see the Format Cells box. From the box select Custom as a category.
After that, from the type option select the format “[mm]”. Press OK.

In the end, we can see all the time values in [mm] format.

9.3. Seconds Format [ss]

In this method, we will convert the time data into a second format. Here, we will use the dataset of our previous method which was the [mm] format.

First, select cell ranges (E6:E10).

Now, right-click and select the option Format Cells.

Then, we can see the Format Cells box. From the box select Custom as a category.
After that, from the type option select the format “[ss]” and press OK.

Finally, we can see all the time values in [mm] format.

Things to Remember

During time calculation we should use a proper format similar to our result.
How you format the result cell has a big impact on the time computation.

Download Practice Workbook

You can download the practice workbook from here.

Sum Time.xlsx

Conclusion

In this article, we have covered various methods to sum time in Excel. You will find our practice workbook added to this article. Download the workbook and practice yourself to discover more. If you feel any confusion just leave a comment in the below box, and we will get back to you as soon as possible.