You may find that while doing payroll or working with other time-related calculations, you need to add hours to a time value in Excel.
So in this tutorial, we will go over the main ways of adding hours to time and calculating elapsed time.
In addition, we will show you how to add minutes and seconds to a time when needed.
So let’s get started.
Method #1: Add Hours to Time using the SUM Operator
The simplest way of adding, the correctly formatted hours, to a time value involves using the SUM operator.
In the example below, we have a list of times in column A and the hours we would like to add to each respective time, listed in column B.
We apply the following steps to get our desired result.
- In cell C2, enter the following formula.
=A2+(B2/24)
- Drag the formula down the column to see the following.
Short explanation: This formula is based on the logic that a complete day is made up of 24 hours. So we have to represent the numbers in column B, as a fraction of 24.
We are taking the initial time and adding the hours of interest.
The number representing the hours we want to add is converted to the proper time hour value by dividing it by 24.
This is then added, since hours are now in the correct format, to the start time to deliver the final result.
Note: This is a very simple method for adding hours to time, which works when we are not adding an hourly amount that is greater than 24 hours.
Also read: How to Add Days to a Date in Excel
Method #2: Add Hours to Time using the TIME Function
The Time Function can also be used to add hours to a time.
In the following example, we have a list of times in column A and the hours we would like to add to each time, listed in column B.
We apply the following steps to get our desired result.
- In cell C2, enter the following formula.
=A2+TIME(B2,0,0)
- Drag the formula down the column to see the following.
Short explanation: The TIME Function is used to return a decimal number that represents a certain time. Remember Excel stores dates and times as numbers.
The syntax of the TIME Function is:
=TIME(hour, minute, second).
In our formula, we take the initial time in cell A2, and then using the TIME Function we enter the hours from B2.
This is so that the value in B2 can be converted to the properly formatted decimal number. So hours will be converted to a fraction of 24.
Note: The Time Function does not allow one to add or subtract values that are greater than 23 hours, or 59 minutes, or 59 seconds. When working with these values, you have to use either the sum or subtraction operator if you have an hourly amount to be added that is greater than 24 hours.
Also read: How to Calculate Hours between two Times in Excel?
Method #3: Add over 24 Hours Time in Excel Using the Manual Method
We can use the manual method to add an hourly amount that is greater than 24 hours, cumulatively.
In our example below, we have a list of students in column A and the time they took to complete their respective tasks shown in column B.
We want to return the sum of all the hours in cell B12.
We apply the following steps to get our desired result.
- So in cell B12, enter the following formula.
=B2+B3+B4+B5+B6+B7+B8+B9+B10+B11
- Now we have to apply the correct formatting. Right-click cell B12 and choose Format Cells…
- Using the Format Cells Dialog Box, select the Custom option as the Category and type [h]:mm in the Sample section. Click Ok.
4) Click Ok.
Also read: How to Calculate Elapsed Time in Excel
Method #4: Add over 24 hours in Excel Using the TEXT and SUM Function
Another way of adding amounts that exceed 24 hours is by using the TEXT Function in conjunction with the SUM Function.
We will use the same example as the one given above.
We apply the following steps to get our desired result.
- So this time in cell B12, enter the following formula.
=TEXT(SUM(B2:B11),"[h]:mm")
Short explanation: Remember that Excel stores TIME as a number. The TEXT Function is used to format numbers by the use of format codes.
The syntax of the TEXT Function is:
=TEXT(value, format_text)
- where value is the input number. In this case the number returned is from the SUM Function.
- format_text is the code that is used to specify the way the number is displayed which in this case is [h]:mm. This means that we display the hours and the minutes, if we specified only h then we would have seen only the hours and no minutes.
So in summary in this formula we are adding all the hours in column B, then using the [h]:mm code, we format the hours accordingly to give us the correct result.
You will see that the value is now left-aligned since we used the TEXT Function as compared to the result obtained from our previous example.
Also read: Convert Decimal To Fraction Excel
Method #5 Calculate the Number of Hours Between Two Times Using The Subtraction Operator
A fairly common question involving time that you will come across involves how to show the difference between the two time values in hours.
In our example below, we have a list of start times and end times for each employee.
We would like to calculate how many hours each respective employee worked.
We apply the following steps to get our desired result.
- In cell D2, enter the following formula.
=C2-B2
- Select cell D2 and press Ctrl 1 on your keyboard, in order to launch the Format Cells Dialog Box.
- Using the Format Cells Dialog Box, choose the Custom option as the Category and type h:mm as the Sample, and then click Ok.
- You should see the following.
- Drag the formula down the column to see the following.
Note: This formula only works, when the time difference between the two given time values is not greater than 24 hours. Also, always remember when using the subtraction operator, to put the later time first.
Also read: Convert GMT to EST in Excel
Method #6: Adding Minutes Using the SUM operator
We can also add minutes to a time value in Excel.
In the following example, we have a list of start times in column B and the minutes we would like to add to the starting time in column C.
We apply the following steps to get our desired result.
- In cell D2, enter the following formula.
=B2+(C2/1440)
- Drag the formula down the column.
This formula is based on the logic that we have 1440 minutes in a day. We take the original time and add the minutes of interest formatted correctly.
The number representing the minutes we want to add, is converted to a time minute value by dividing it by 1440.
This is then added in the correct format, to the start time to deliver the end time.
Also read: Convert Time to Decimal in Excel
Method #7: Adding Minutes to Time using the Time Function
The Time Function can also be used to add minutes to a time value.
In the following example, we have a list of start times in column B and the minutes we would like to add to each time, listed in column C.
We apply the following steps to get our desired result.
1) In cell D2, enter the following formula.
=B2+TIME(0,C2,0)
2) Drag the formula down the column to see the following.
Explanation of the formula: In our formula, we are taking the initial time in cell B2.
Consequently, we are using the TIME Function in cell D2 to format the numbers from C2 correctly, so that these can be converted to a decimal numbers.
Excel then adds the seconds in their correct format (which is a value divided by 1440) in order to return the result in cell D2.
Also read: Add Months to a Date in Excel
Method #8: Add Seconds to Time using the TIME Function
We can use the TIME Function to add seconds to a time value. In this case we specify that the hours and minutes are 0.
In the following example, we have a list of start times in column B and the seconds we would like to add to each time, listed in column C.
We apply the following steps to get our desired result.
- In cell D2 enter the following formula.
=B2+TIME(0,0,C2)
- Drag the formula down the column to see the following.
Note: This method works when the seconds we want to add are less than 60 seconds.
Method #9: Add Hours to Time Using VBA
We have a list of student names in column A, the time they started in column B, and the hours they have taken to complete their respective task.
We want to calculate the end time using VBA.
We apply the following steps to get our desired result.
- Select range B2:B11.
- Go to the Developer Tab and in the Code Group and the click on the Visual Basic option.
- Go to Insert and select Module.
- Copy and paste the following code into the module code window.
'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Adding_Hours_To_Time()
Dim result1 As Variant
For Each cell In Selection
result1 = CDate(cell + (cell.Offset(0, 1) / 24))
cell.Offset(0, 2).Value = result1
Next
End Sub
- Press F5 to run the code. You should see the following.
In the above code, we have used the For Each statement to go through each cell in the selection, and then get the result In column D (which is two columns to the right)
So as soon as you run the code, it is going to add the number of hours in column C to the start time in column B and give us the result in column D
In this article, we went through simple ways to add hours to time in Excel, which will help you when doing time tracking and other time-related calculations.
We also covered the simple ways of adding minutes and seconds to a time value in Excel.
Other Excel articles you may also like:
