Excel always stores time and duration in the time format. You can format cells to show the duration as hours and minutes, but the formula bar will always show that as a clock time. Assume you have a spreadsheet to record the weekly working hours of employees. You want to get the total working hours for each employee in the Total column.
Step 1 – Enter the SUM Formula to Get Total Duration
- Enter the following formula in cell H5.
=SUM(C5:G5)
Step 2 – Apply the [h]:mm Format
- Press Ctrl + 1 while you are in cell H5 to open the Format Cells dialog box.
- Select the Custom category from the Number tab.
- Enter [h]:mm in the Type field and click OK.
Step 3 – Copy the Formula and Format
- Drag the Fill Handle icon to copy the formula to the cells below.
You can also format the hours and minutes as Xh Xm.
- Select the range H5:H10 to do that.
- Press Ctrl + 1 to open the Format Cells dialog box.
- Enter [h]“h” m“m” in the Type field as the custom number format.
- Click OK.
- You will see the following result.
Download the Practice Workbook
Related Articles
<< Go Back to Time Format | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
