How to Sum Time in Excel (9 Suitable Methods)

In this article, we will demonstrate to you how to sum time in Excel. While working in Microsoft 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.


Download Practice Workbook

You can download the practice workbook from here.


9 Methods to Sum Time in Excel

1. Use of Conventional approach 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.

Use of Conventional approach to Sum time in Excel

  • Firstly, select cell E6 and insert the following formula:
=C6+D6

Use of Conventional approach to Sum time in Excel

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

Use of Conventional approach to Sum time in Excel

  • 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.

Use of Conventional approach to Sum time in Excel

Read More: [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)


2. Excel SUM Formula to Sum Time

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 total working hours on Thursday and Friday for John.

Excel SUM Formula to Sum Time

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

Excel SUM Formula to Sum Time

We can use the Fill Handle tool also 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.

Excel SUM Formula to Sum Time

Read More: How to Add Hours to Time in Excel (8 Quick Ways)


3. Use AutoSum 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:

Use AutoSum to Add TIME in Excel

  • In the beginning, select cell range E6:E10.

Use AutoSum to Add TIME in Excel

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

Use AutoSum to Add TIME in Excel

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

Use AutoSum to Add TIME in Excel

Read More:


4. Sum Time with Excel TIME Function

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.

Sum Time with Excel TIME Function

  • Finally, drag the Fill Handle tool to cell D10. By doing this we have added 2 hours 10 minutes and 50 seconds with 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. Apply TEXT Function for Time Sum up 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 lap’s in 3 different formats like ‘h’, ‘h:mm’, ‘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.

Apply TEXT Function for Time Sum up in Excel

Here h converts the added value in an hour format. 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.

Apply TEXT Function for Time Sum up in Excel

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.

Apply TEXT Function for Time Sum up in Excel

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 lap’s for all the runners in all three formats.


6. Add 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 Under 24 Hours Add up

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

Under 24 Hours

  • 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.

Under 24 Hours

  • 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 hours with cell C6.

Read More: How to Calculate Total Hours in Excel (9 Easy Methods)


6.2 Add 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.

Under 60 Minutes

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

Over 24 Hours

  • 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.

Over 24 Hours

  • 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.

Read More: How to Add Time in Excel Over 24 Hours (4 ways)


7. 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 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.

Under 60 Minutes

  • 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) increase the cell value by 25 minutes.


7.2 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.

Over 60 Minutes

  • Finally, drag the Fill Handle tool to cell D10.

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

Read More: How to Add Minutes to Time in Excel (5 Easy Ways)


8. Excel Sums Seconds

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 Under 60 Seconds Sum up

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.

Under 60 Seconds

  • 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 with cell C6.


8.2 Sum Total 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.

Over 60 Seconds

  • 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. Sum Time and Format as Hours, Minutes and Second in Excel

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

  1. Hours Format. [hh]
  2. Minutes Format. [mm]
  3. 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.

Hours Format[hh]

  • At first, select cell (E6:E10).

Hours Format[hh]

  • Next, do right click and select the option Format Cells.

Hours Format[hh]

  • 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.

Hours Format[hh]

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

Read More: Calculate Hours Between Two Times in Excel (6 Methods)


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).

Minutes Format [mm]

  • Next, do right click and select the option Format Cells.

Minutes Format [mm]

  • 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.

Minutes Format [mm]

  • 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 seconds format. Here, we will use the dataset of our previous method which was the [mm] format.

  • First, select cell (E6:E10).

Seconds Format [ss]

  • Now, do right click and select the option Format Cells.

Seconds Format [ss]

  • 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.

Seconds Format [ss]

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

Read More: Excel Formula To Calculate Time Worked


Things to Remember

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

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, we will revert to you as soon as possible.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo