Below, learn 4 easy methods for adding Hours and Minutes in Excel. The following dataset contains 3 columns: Employee Name, Working Time, and Extra Time.
Method 1 – Using SUM Function to Add Hours and Minutes in Excel
You can apply the SUM function to add hours and Minutes in Excel. In addition, the SUM function will work in time format.
Steps:
- Use the corresponding formula in cell E5.
=SUM(C5:D5)The SUM function will sum up all the times. C5:D5 denotes the data range to add.
- Press ENTER to get the result.
The result is 8:00, although it should be 32:00. But the SUM function returns the result in a 24-hour format.
- Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E10.
If your added time doesn’t surpass 24 hours you don’t have to follow the steps below.
But, as there are more than 24 hours in my example, I have to modify the result.
- Select the output E5:E10.
- Press the CTRL+1 keys to open the Format Cells dialog box directly.
Use the Context Menu Bar or the Custom Ribbon to go to the Format Cells command. In the case of using the Context Menu Bar, select the data range >> Right-click on the data >> choose the Format Cells option.
If using Custom Ribbon, select the data range >> from the Home tab >> go to the Format feature >> choose the Format Cells command.
A dialog box named Format Cells will appear.
- From the dialog box, make sure that you are on the Number command.
- Go to the Custom option.
- Type [h]:mm in the Type: box.
- Press OK to get the changes.
You will see the added Hours and Minutes.
Method 2 – Combining TEXT and SUM Functions to Add Hours and Minutes
Steps:
- Use the following formula in cell E5.
=TEXT(SUM(C5:D5),"[h]:mm")
Formula Breakdown
- The SUM function will sum up all the times of the given range.
- SUM(C5:D5)—> turns 8:00.
- The TEXT function will return a value in text with a given format.
- “[h]:mm” denotes the format. Which will convert the time into added hours.
- TEXT(8:00,”[h]:mm”)—> becomes 32:00.
- Press ENTER to get the result.
- Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E10.
You will see the following added hours and minutes.
Method 3 – Adding Hours and Minutes in Excel with Generic Formula
Steps:
- Use the following formula in cell E5.
=C5+D5In this formula, there are two cell values that use the Plus (+) sign.
- Press ENTER.
You can see the result is 1:00, although the actual total is 25:00. This is because the formula counts the cell values as time.
- Double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E10.
The formula returns the result in a 24-hour format. If the added time does not surpass 24 hours, you don’t need to follow the steps below.
- Select the output E5:E10.
- Press the CTRL+1 keys to open the Format Cells dialog box directly.
Use the Context Menu Bar or the Custom Ribbon to go to the Format Cells command. In the case of using the Context Menu Bar, select the data range >> Right-click on the data >> choose the Format Cells option.
If using Custom Ribbon, select the data range >> from the Home tab >> go to the Format feature >> choose the Format Cells command.
A dialog box named Format Cells will appear.
- From the dialog box, make sure that you are on the Number command.
- Go to the Custom option.
- Type [h]:mm in the Type: box. Here, you can see the sample immediately in the Sample box.
- Press OK to get the changes.
You will get all the added hours and minutes.
Read More: How to Add Time in Excel Over 24 Hours
Method 4 – Applying TIME, HOUR, & MINUTE Functions
You can apply the TIME, HOUR, and MINUTE functions to add hours and minutes in Excel. The TIME function will return the result in time format. This method will add 1 hour and 45 minutes.
Steps:
- Use the following formula in cell E5.
=TIME(HOUR(C5)+1,MINUTE(C5)+45,0)
Formula Breakdown
- The MINUTE function extracts only the minutes from a given time.
- MINUTE(C5) —> becomes 30.
- The HOUR function extracts only the hours from a given time.
- HOUR(C5) —> turns 8.
- The TIME function converts a number into time.
- TIME(8+1,30+45,0) —> gives 10:15.
- Press ENTER to get the result.
- Double-click on the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E10.
The result is in 24-hour time format.
Read More: How to Add Hours, Minutes, and Seconds in Excel
Things to Remember
- Here, method 2 (TEXT) will return the exact result. This means you will get the added hours not the added time.
- In the case of other methods, you don’t need to do formatting when the added hours will be less than 24 hours. If the added values cross 24 hours, then you must format.
Download Practice Workbook
You can download the practice workbook from here:
Related Articles
- How to Add 30 Minutes to Time in Excel
- How to Add Minutes to Time in Excel
- How to Add Minutes and Seconds in Excel
- How to Add Time to Date in Excel
- How to Add 1 Hour to Time in Excel
- Add 15 Minutes to Time in Excel
- Add Milliseconds to Time in Excel
- How to Add Hours to Time in Excel
- Add 8 Hours to Time in Excel
<< Go Back to Add Time in Excel | Calculate Time | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
