We often need to add time in Excel for various time-related calculations. Time-tracking, calculating work hours, preparing employee shift schedules, event planning, calculating time zones, etc. are some common instances of adding time in Excel.
In this article, we will present various examples to add time in Excel. We will add hours, minutes, and seconds to a given time using the TIME function and basic arithmetic operators. Apart from the formula, we also have to apply various time formats while summing up multiple time values. to get the output in the required format.
We can use the basic arithmetic Plus (+) operator or the SUM function to add multiple time values and calculate total time in Excel. We have to apply various cell formatting here as well. However, instead of applying the cell formatting manually, we can use the TEXT function to show the calculated time value in the required format.
While preparing this article, we used Excel 365 for our calculations. However, the functions and features used in this article are available in Excel Starter 2010 or newer versions.
Click the image for a detailed view
Download Practice Workbook
How to Add Time in Excel?
We can add specific hours, minutes, or seconds with an existing time value. Apart from applying a formula to add time, we also have to change the cell formatting to get the required format of the time value.
Here, we will use the following dataset to present the methods to add time. It contains due dates for several projects. We will update the due dates by adding hours, minutes, and seconds to these due dues.
Click the image for a detailed view
How to Add Hours to Time in Excel?
We can add the required hours from a given time using the TIME function or basic arithmetic operators. The TIME function is helpful for adding less than 24 hours. On the other hand, we can use the basic arithmetic operators to add any number of hours.
1.1 Add Less Than 24 Hours
- In Cell D5, insert the following formula >> press Enter key >> use the Fill Handle tool.
=C5+TIME($F$5,0,0)
You can click the image for a detailed view
- Here, the output appeared as a Number. So, we need to change the Number Format. Click on the dialogue box launcher (tilted down arrow) icon of Number Format.
Click the image for a detailed view
- A user form like the following will pop up. From the Number tab, go to the Custom category >> type m/d/yyyy h:mm:ss AM/PM >> click on the OK button.
- This will convert the output into the required format.
Click the image for a detailed view
1.2 Add Any Number of Hours
The formula used in the previous case is not effective for time values over 24 hours. We can apply a formula using simple arithmetic operators that will apply to times values both under and over 24 hours.
- Insert the following formula in Cell D5 >> press the Enter key >> drag down the Fill Handle icon >> change the Number Format if required.
=C5+($F$5/24)
You can click the image for a detailed view
Here, the value 24 represents the number of hours in a day.
How to Add Minutes to Time?
The process of adding minutes from a time value is similar to adding hours. We will use the TIME function and basic arithmetic operators here as well.
2.1 Add Less Than 60 Minutes
- Insert the formula below in Cell D5 >> press Enter key >> use the Fill Handle tool >> modify the Number Format if required.
=C5+TIME(0,$F$5,0)
You can click the image for a detailed view
2.2 Add Any Number of Minutes
- For a more effective formula that applies to both under or over 60 minutes, in Cell D5, insert the following formula >> press the Enter key >> drag down the Fill Handle icon >> change the Number Format if required.
=C5+($F$5/1440)
Click the image for a detailed view
Here, 1440 depicts the total minutes in a day.
How to Add Seconds to Time in Excel?
Like the previous two methods to add hours and minutes, we will use the TIME function to add less than 60 seconds and basic arithmetic operators to add any number of seconds.
3.1 Add Less Than 60 Seconds
- In Cell D5, enter the formula below >> press the Enter key >> drag down the Fill Handle tool >> modify the Number Format if required.
=C5+TIME(0,0,$F$5)
You can click the image for a detailed view
3.2 Add Any Number of Seconds
- In Cell D5, insert the formula below >> press the Enter key >> use the Fill Handle tool >> change the Number Format if required.
=C5+($F$5/86400)
Click the image for a detailed view
Here, the value 86400 represents the number of seconds in a day.
Read More: Add Milliseconds to Time in Excel
How to Sum Time in Excel?
We can apply arithmetic operators, the SUM function, or a combination of TEXT and SUM functions to add multiple time values to calculate total time in Excel. We also need to understand various Time formats for the calculated total time value.
What Is the Simple Formula Using Arithmetic Operator to Sum Time in Excel?
We can use the arithmetic Plus (+) operator between two values or cell references to sum time.
- For the following dataset, we can apply the formula below in Cell C9 and press Enter key.
=C5+C6+C7
- Here, the output appeared as a Number. To convert it to a suitable format, click on the dialogue box launcher (tilted down arrow) icon of Number Format.
- A user form like the following will pop up. From the Number tab, go to the Custom category >> type h:mm:ss >> click on the OK button.
This will convert the output to the required format. Note that, the calculated sum is lower than 24 hours. If the output had been higher than 24 hours, the h:mm:ss format would have created some problems. We’ll discuss this in the next section.
How to Use the SUM Function to Add Time in Excel?
Manually selecting each cell and typing the arithmetic Plus (+) operator can be tedious for large datasets. We can use the SUM function to resolve this problem.
- In Cell C12, enter the following formula >> press the Enter key >> change the Number Format to h:mm:ss if required.
=SUM(C5:C10)
However, note that the output is much lower than what the actual output (42:15:30) should be. This is because the actual output is higher than 24 hours and in h:mm:ss format, 24 hours are subtracted when a value is higher than 24 hours.
- We can resolve this by applying a different format [h]:mm:ss.
- Now, we can see the actual output.
- We can also apply various other formats. These formats and the corresponding outputs are shown below:
You can click the image for a detailed view
Read More:
How to Combine TEXT and SUM Functions to Add Time in Excel?
Instead of manually applying a format, we can combine the TEXT and SUM functions to apply any specific format.
- Insert the following formula in Cell B13 >> press Enter key >> drag down the Fill Handle icon.
=TEXT(SUM($C$5:$C$10),C13)
Click the image for a detailed view
What Are the Things to Remember?
- We may require changing the format of the output to custom date formats to get the output in the required format.
- You can apply the time formatting in the TEXT function instead of manually applying it through the Format Cells dialog box.
- Formulas and time formats are different for time values lower and higher than 24 hours, 60 minutes, or 60 seconds.
Frequently Asked Questions
1. What are the shortcuts for quickly adding time in Excel?
Answer: We can apply the SUM function or the ∑ AutoSum button to quickly add multiple time values in Excel.
2. How do I handle time calculations that exceed 24 hours in Excel?
Answer: In Excel, time values are typically limited to 24 hours, which means that time calculations exceeding 24 hours may result in incorrect values. However, Excel provides a way to handle time calculations that exceed 24 hours by using the custom format “[h]:mm:ss“.
3. How to Add Time in Excel Automatically?
Answer: We can apply the Nested IF function or a VBA Code to add time automatically. For example, if we need to automatically add 8 hours to the time value in Cell A1, then we can apply the following formula in Cell B1.
=IF(A1<>"",TEXT(A1+TIME(8,0,0),"hh:mm"),"")Add Time in Excel: Knowledge Hub
Conclusion
This concludes our article on how to add time in Excel. We discussed formulas for adding hours, minutes, or seconds to a specific time in Excel. We also showed how to use the basic arithmetic Plus (+) operator and SUM function to add multiple time values. Apart from these formulas, we also showed how to show our calculated value in different time formats using the Format Cells dialog box and the TEXT function. We hope that the demonstrated examples were helpful in your quest to learn how to add time in Excel. Let us know your feedback on the article.
<< Go Back to Calculate Time | Date-Time in Excel | Learn Excel
