Md. Shamim Reza
This article illustrates how to format hours and minutes as duration but not as time in excel. 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.
For example, assume you have entered 10:30 in a cell to record the 10 hours and 30 minutes working period of an employee. The cell will show that but the formula bar will show 10:30:00 AM. This may not be a problem if you only want to display the duration. But, you may not get accurate results while adding or subtracting them. You will need to format the hours and minutes properly in that case.
Download Practice Workbook
You can download the practice workbook from the download button below.
Steps to Format Hours and Minutes in Excel as Duration Not as Time
Assume you have a spreadsheet to record the weekly working hours of employees. Now you want to get the total working hours for each employee on the Total column.
Step 1: Enter SUM Formula to Get Total Duration
- Then, enter the following formula in cell H5.
=SUM(C5:G5)
Step 2: Apply [h]:mm Format
- But the result returned by the formula is far from accurate. Now press CTRL+1 while you are in cell H5 to open the Format Cells dialog box. Then select the Custom category from the Number tab. Next, enter [h]:mm in the Type field and click OK.
Step 3: Copy the Formula and Format
- After that, you will get an accurate result. Now drag the Fill Handle icon to copy the formula to the cells below.
- You can also format the hours and minutes as 37h 30m. First, select the range H5:H10 to do that. Then press CTRL+1 as earlier to open the Format Cells dialog box. Then enter [h]“h” m“m” in the Type field as the custom number format. Click OK after that.
- Finally, you will see the following result.
Read More: Convert Number to Hours and Minutes in Excel (2 Easy Methods)
Things to Remember
- You must use the square brackets around the hour format to get an accurate result.
- You can either format the cells first and then enter values or do the opposite, both works the same.
Conclusion
Now you know how to format hours and minutes not as time but as the duration in excel. Do you have any further queries or suggestions? Please let us know using the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.
