How to Add Hours and Minutes in Excel (4 Suitable Methods)

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.

Dataset for Adding Hours and Minutes in Excel


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.

Applying SUM Function for Adding Hours and Minutes in Excel

  • 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")

Applying TEXT function with SUM Function for Adding Hours and Minutes in Excel

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+D5

In 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.

Employing Generic Formula for Adding Hours and Minutes in Excel

  • 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.


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)

Use of Combined Functions for Adding Hours and Minutes in Excel

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


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

Get FREE Advanced Excel Exercises with Solutions!
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo