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

Get FREE Advanced Excel Exercises with Solutions!

If you want to add hours to time in Excel easily, then you can follow this article to learn various ways to do this job. So, let’s get into the main article to explore those ways.


How to Add Hours to Time in Excel: 8 Ways

Here, we have the following two datasets; one is for the Entry Time, and working Time Period of the employees of a company and the other contains the records of the Order Time, Duration between the order time, and delivery time of the products of another company.
Using these datasets, we will illustrate the ways to add hours to time in Excel easily.

We have used the Microsoft Excel 365 version here, you can use any other version according to your convenience.


Method-1: Add Hours to Time in Excel for Less than 24 Hours

Here, we will normally add the Entry Time with the Time Period to get the Exit Time of the employees, and here the results after summing up will be less than 24 hours so no extra step will be needed here.
For adding up the times simply with the addition operator you have to keep both of the values in Time format like below.

addition for less than 24 hours

Steps:
➤ Use the following formula in cell E5.

=C5+D5

Here, C5 is the Entry Time, and D5 is the Time Period.

addition for less than 24 hours

➤ Press ENTER and drag down the Fill Handle tool.

addition for less than 24 hours

After adding up hours with the Entry Times we are getting the following Exit Times.

addition for less than 24 hours

Read More: How to Add 1 Hour to Time in Excel 


Method-2: Add Hours to Time in Excel for More than 24 Hours

To get the Exit Times more than 24 hours after adding up hours with the Entry Times, we have increased the hours of the Time Periods in this example.

addition for more than 24 hours

Steps:
➤ Use the following formula in cell E5.

=C5+D5

Here, C5 is the Entry Time, and D5 is the Time Period.

addition for more than 24 hours

➤ Press ENTER and drag down the Fill Handle tool.

addition for more than 24 hours

So, we can see that after adding up values we are not getting our expected Exit Times because for equal to or more than 24 hours Excel will consider 24 hours into a day and then show up only the remaining hours and minutes as results.

addition for more than 24 hours

To solve this problem select the Exit Times and then go to Home Tab >> Number Format dialog box symbol.
You can go there also by clicking CTRL+1.

addition for more than 24 hours

Then the Format Cells dialog box will pop up.
➤ Go to Number Option >> Custom Option >> write [h]:mm in the Type box >> press OK.

addition for more than 24 hours

Afterward, we will get our real added values for more than 24 hours.

addition for more than 24 hours

Read More: How to Add Time in Excel Over 24 Hours 


Method-3: Add Hours to Time in Excel Using the TIME Function

Here, we will use the TIME function to add up hours with the Entry Times to get the Exit Times and you can keep the hours of the Time Periods in General format here.

TIME function

Steps:
➤ Type the following formula in cell E5.

=TIME(HOUR(C5)+D5,MINUTE(C5),SECOND(C5))

Here, C5 is the Entry Time, and D5 is the Time Period.

  • HOUR(C5)+D5 → 11+9
    Output → 20
  • MINUTE(C5) → 30
  • SECOND(C5) → 0
  • TIME(HOUR(C5)+D5,MINUTE(C5),SECOND(C5)) → becomes
    TIME(20,30,0)
    Output → 20:30

TIME function

➤ Press ENTER and drag down the Fill Handle tool.

TIME function

Finally, we are getting the Exit Times after adding up the hours of the Time Periods with the Entry Times.

TIME function


Method-4: Add Hours to Time in Excel for Negative Hours

Suppose, we have some negative hours as Time Periods and here we will add these negative hours with the Entry Times.

negative hours

Though it’s quite unusual to have a negative time period, we are showing through this dataset to maintain consistency and simplicity.

Steps:
If we use the following formula like the previous method, then we will have #NUM! error due to the negative results as time can’t be negative.

=TIME(HOUR(C5)+D5,MINUTE(C5),SECOND(C5))

negative hours

So, we will solve this problem by using the following formula

=TIME(IF(HOUR(C5)+D5<0,24+HOUR(C5)+D5,HOUR(C5)+D5),MINUTE(C5),SECOND(C5))

Here, C5 is the Entry Time, and D5 is the Time Period.

  • HOUR(C5)+D5<0 → 11-9<0 → 2<0
    Output → FALSE
  • IF(FALSE,24+HOUR(C5)+D5, HOUR(C5)+D5) → as it is FALSE so it will execute the 3rd argument
    Output → 2
  • TIME(IF(HOUR(C5)+D5<0,24+HOUR(C5)+D5,HOUR(C5)+D5),MINUTE(C5),SECOND(C5)) → TIME(2,30,0)
    Output → 2:30

negative hours

➤ Press ENTER and drag down the Fill Handle tool.

negative hours

Instead of getting #NUM! error, now, we are adding up to 24 to those negative values.

negative hours


Method-5: Add Hours to Time in Excel for a List of Date Time

Here, we have the date and time combinations in the Order Time column, and with these times we will add up the hours of the Durations to get the Delivery Times.

adding hour to date time

Steps:
If we use the following formula then instead of adding hours we will add the duration to the days,

=C5+D5

adding hour to date time

We can rectify that formula by dividing the hours of the Durations by 24 to convert the day into hours. (1 day = 24 hours)

=C5+D5/24

adding hour to date time

 

➤ Press ENTER and drag down the Fill Handle tool.

adding hour to date time

 

As a result, we can successfully add the hours to the Order Times to get the Delivery Times (m-d-yy h: mm AM/PM) now.

adding hour to date time

Read More: How to Add Minutes to Time in Excel 


Method-6: Using the TIME Function to Add Hours to Date Time

In this section, we will add hours to the Order Times by using the TIME function.

TIME Function for date-time

Steps:
➤ Type the following formula in cell E5.

=C5+TIME(D5,0,0)

Here, C5 is the Order Time, and D5 is the Duration. TIME will convert the duration into hours and then this hour will be added up with the Order Time.

TIME Function for date-time

➤ Press ENTER and drag down the Fill Handle tool.

TIME Function for date-time

Eventually, we are getting the Delivery Times for the products.

TIME Function for date-time

Read More: How to Add Time to Date in Excel 


Method-7: Combining TIME, MOD and INT Functions to Add Hours to Time

You can add hours to the times by using the TIME, MOD, and INT functions.

TIME, MOD,INT function

Steps:
➤ Type the following formula in cell E5.

=TIME(MOD(D5,24),0,0)+C5+INT(D5/24)

Here, C5 is the Order Time, and D5 is the Duration.

  • MOD(D5,24) → MOD(15,24)
    Output → 15
  • TIME(MOD(D5,24),0,0) → TIME(15,0,0)
    Output → 0.625
  • INT(D5/24) → INT(15/24) → INT(0.625)
    Output → 0
  • TIME(MOD(D5,24),0,0)+C5+INT(D5/24) becomes
    TIME(0.625+43474.2708333+0)
    Output → 1-9-19 9:30 PM

TIME, MOD,INT function

➤ Press ENTER and drag down the Fill Handle tool.

TIME, MOD,INT function

Ultimately, you will get the Delivery Times for the products.

TIME, MOD,INT function

Read More: How to Add Hours, Minutes, and Seconds in Excel


Method-8: Using a VBA Code to Add Hours to Time in Excel

Here, we will use a VBA code to add up the Duration hours with the Order Times to get the Delivery Times.

VBA Code

Steps:
➤ Go to Developer Tab >> Visual Basic Option.

VBA Code

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

VBA Code

After that, a Module will be created.

VBA Code

➤ Write the following code

Function Houraddition(value As String, x As Integer) As String

Dim dt1 As Date
dt1 = CDate(value)
dt1 = DateAdd("h", x, dt1)
Houraddition = Format(dt1, "m/d/yy hh:mm AM/PM")

End Function

This code will create the function Houraddition, CDATE will convert the given value into a date and DATEADD will add the hour value to this date. Finally,  will give our desired format to this date-time.

VBA Code

Now, go back to the sheet and write the following formula in cell E5

=Houraddition(C5,D5)

Here, C5 is the Order Time, D5 is the Duration and Houraddition will add up the Duration to the Order Date.

VBA Code

➤ Press ENTER and drag down the Fill Handle tool.

VBA Code

In this way, we will get the Delivery Times for the products.

VBA Code

Read More: Add 8 Hours to Time in Excel 


Practice Section

For doing practice by yourself we have provided the following Practice sections in the sheets named Practice1 and Practice2. Please do it by yourself.

practice

practice


Download Workbook


Conclusion

In this article, we tried to cover the ways to add hours to time in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles


<< Go Back to Add Time in Excel | Calculate Time | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo