Format Hours and Minutes Not Time in Excel (with Quick Steps)

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.

Excel Format Hours and Minutes Not Time


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.

Excel Format Hours and Minutes Not Time

  • You will see the following result.


Download the Practice Workbook


Related Article


<< Go Back to Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo