ExcelInsider Team
Microsoft Excel is one of the most advanced tools for managing time values. However, adding hours, minutes, and seconds can sometimes be tricky. While calculating time you need to use proper methods and formatting for time values. In this article, we will guide you through several methods to add hours, minutes, and seconds in Excel.
To add hours, minutes and seconds in Excel, here is one simple solution by using plus operator between times.
➤ Choose a cell, and apply the formula below to add time values.
=B2+C2
➤ Press ENTER to get the sum of time values in hours, minutes and seconds.
Using Plus Operator Between Times
The easiest way to add two or more time values is by using the simple plus operator (+) between time values. However, you need to choose a proper time formatting of the cell where you want to see the time values in hours, minutes and seconds.
Let’s imagine we have a dataset with employee overtime hours for two days. We will use this data to calculate the total overtime.
➤ Choose a cell and write down the following formula.
=B2+C2
➤ Press ENTER and drag the Fill Handle down to fill the rest of the cells.
As a result, you will get the time added time values in the column showing hour, minutes and seconds.
Sometimes, you will notice that the result is a decimal number or in other formats instead of a time value. This is because Excel stores time as a fraction of a day.
To display the result in the correct time format, you need to format the cells.
➤ Select the cells from the result column.
➤ Press Ctrl + 1  to open the Format Cells dialog box.
➤ In the Format Cells dialog box, go to the Number tab.
➤ From the Category list, select Custom.
➤ In the Type box, enter the following format code –
[h]:mm:ss
➤ Click OK.
Note:
The square brackets around [h] ensure that the total hours will not reset to zero after 24 hours.
Finally, your results will now be displayed in the correct time format.
Applying Excel Functions & Formulas
Excel offers several functions that can be used to add time values. Here, we will explore some of the functions to add hours, minutes and seconds.
SUM Function
The SUM function can add multiple time values at once. It works similarly to the plus operator but is more efficient for a large number of cells.
➤ Select a cell and put the following formula.
=SUM(B2,C2)
➤ Press ENTER and drag down the Fill Handle.
Thus, you will get the time values added in the column. Make sure to format the cells to display the correct time value.
TEXT Function
The TEXT function is useful when you want to convert a numerical value into a text string with a specific format. When adding time, you can use it to format the result directly within the formula.
➤ Choose a cell and write down the following formula.
=TEXT(B2+C2,"[h]:mm:ss")
➤ Hit ENTER and drag the Fill Handle down.
This formula adds the time value of cells B2 and C2 and then formats the result as hour, minutes and seconds.Thus, you don’t need to manually format the cell afterward.
Combining TIME, HOUR, MINUTE and SECOND Functions
For more complex calculations, you can use a combination of the TIME, HOUR, MINUTE, and SECOND functions. This formula adds the individual components of the time values separately before combining them.
➤ Choose a cell and put the formula below.
=TIME(HOUR(B2)+HOUR(C2), MINUTE(B2)+MINUTE(C2), SECOND(B2)+SECOND(C2))
➤ Click ENTER and drag the Fill Handle down to fill.
This formula first extracts the hours, minutes, and seconds from the cells, adds them separately, and then uses the TIME function to assemble a new time value. Finally, providing the time value with added hours, minutes and seconds.
Frequently Asked Questions
Why does Excel show 03:15:45 instead of 27:15:45 when I add times?
Because Excel’s default time format resets after 24 hours. To display total hours above 24, you need to apply a custom number format like [h]:mm:ss.
Why does Excel show ##### instead of the time result?
This happens when the cell is too narrow or when the result is a negative time value. Widen the column or check the calculation.
Can I add time values that are stored as text?
No, text values won’t calculate directly. You need to convert them into time format using TIMEVALUE function before adding.
Concluding Words
Above we have explored several methods to add hours, minutes and seconds in Excel. Whether you use the simple plus operator for quick calculations, the SUM function for multiple time values, the TEXT function for direct formatting, or a combination of TIME, HOUR, MINUTE, and SECOND functions for precise result, Excel provides all solutions that fits your needs. If you have any other questions, feel free to ask them in the comments section below.
