How to Add Hours to Time in Excel?

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.

data set where hours need to be added to time

We apply the following steps to get our desired result.

  1.  In cell C2, enter the following formula.
=A2+(B2/24)
formula to add hours to time
  1. Drag the formula down the column to see the following.
apply the formula for the entire column

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.

data set with time and hours

We apply the following steps to get our desired result.

  1.  In cell C2, enter the following formula.
=A2+TIME(B2,0,0)
formula to add hours to time
  1. Drag the formula down the column to see the following.
apply the formula for the entire column

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.

data set with time taken to complete task

We apply the following steps to get our desired result.

  1. So in cell B12, enter the following formula.
=B2+B3+B4+B5+B6+B7+B8+B9+B10+B11
simple edition arithmetic to add total time
  1. Now we have to apply the correct formatting. Right-click cell B12 and choose Format Cells…
  1.  Using the Format Cells Dialog Box, select the Custom option as the Category and type [h]:mm in the Sample section. Click Ok.
specify the time format in format cells dialog box

4) Click Ok.

total hours calculated in the right format
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.

  1. So this time in cell B12, enter the following formula.
=TEXT(SUM(B2:B11),"[h]:mm")
text formula to add hours

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.

data set with start time and end time

We apply the following steps to get our desired result.

  1. In cell D2, enter the following formula.
=C2-B2
formula to calculate number of hours worked
  1. Select cell D2 and press Ctrl 1 on your keyboard, in order to launch the Format Cells Dialog Box.
  1. Using the Format Cells Dialog Box, choose the Custom option as the Category and type h:mm as the Sample, and then click Ok.
setting the custom format for time
  1. You should see the following.
hours worked result in hours
  1. Drag the formula down the column to see the following.
drag the formula to copy it for the entire column

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. 

data set with start time and time. in minutes

We apply the following steps to get our desired result.

  1. In cell D2, enter the following formula.
=B2+(C2/1440)
formula to get the end time
  1. Drag the formula down the column.
drag the formula for the entire 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.

data set with start time

We apply the following steps to get our desired result. 

1) In cell D2, enter the following formula.

=B2+TIME(0,C2,0)
formula to add minutes to start time and get the end time

2) Drag the formula down the column to see the following.

copy the formula for the entire column

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.

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. 

data set with start time

We apply the following steps to get our desired result.

  1. In cell D2 enter the following formula.
=B2+TIME(0,0,C2)
formula using the time function
  1. Drag the formula down the column to see the following.
copy formula for the entire column

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.

data set with start time and time taken in hours

We apply the following steps to get our desired result.

  1. Select range B2:B11.
select the start time data range
  1. Go to the Developer Tab and in the Code Group and the click on the Visual Basic option.
click on the visual basic icon in the ribbon
  1. Go to Insert and select Module.
insert a module
  1. 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
  1. Press F5 to run the code. You should see the following.
run the code to get the end time

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment