Convert Number to Hours and Minutes in Excel (2 Easy Methods)

The following table has the Days and Number columns. We will use the Number column of this table to convert number to hours and minutes in Excel.

Method 1 – Using TEXT Function to Convert Number to Hours and Minutes in Excel

Copy the following formula into cell D5:

=TEXT(C5/24,"[h] ""hours,"" m ""minutes""")

Formula Breakdown

TEXT(C5/24,”[h] “”hours,”” m “”minutes”””) → the TEXT function put in a formatting to a number and represent the number in a new way.
C5/24 → divides cell C5 by 24.
- Output: 0.0833333333
- Explanation: Since C5 has an integer part, this is counted as a day value. Therefore, we need to divide cell C5 by 24 to make it an hour value.
“[h] “”hours,”” m “”minutes””” → these are the Format Codes for the Text function. Here, we surround the hour, “h” inside a square bracket. This is because we want the values to surpass twenty-four hours. Without the square bracket, the hours will begin from zero for every twenty-four hours.
TEXT(0.083333333,”[h] “”hours,”” m “”minutes”””) → becomes
- Output: 2 hours, 0 minutes

Press Enter. You can see the result in cell D5.

Drag down the formula with the Fill Handle tool.

You can see the conversion of number to hours and minutes in the Hours and Minutes column.

Method 2 – Dividing and Formatting Numbers

Step 1 – Dividing Numbers by a Day (24 Hours)

Insert the following formula in cell D5:

=C5/24

This simply divides the cell C5 value by 24.

Press Enter.
Drag down the formula with the Fill Handle tool.

Therefore, you can see a complete column, which shows decimals.

Step 2 – Setting a Format

Select the cells D5:D9.
Right-click and select Copy from the Context Menu.

Select the cells E5:E9 of the Hours and Minutes column.
Go to the Home tab and select the Paste option.
From Paste Values, select Values & Number Formatting.

As a result, you can see the numbers in the Hours and Minutes column.

Select the values in the Hours and Minutes column.
From the Home tab, go to the Number group.
Click on the Number Format which is marked with a red color box. Select the anchor to bring out a Format Cells dialog box.

You can also bring out the Format Cells dialog box by pressing Ctrl + 1.

A Format Cells dialog box will appear.
Go to the Number group.
Select Time from Category.
Choose a Type. We picked 1:30 PM as the Type because we only want to show hours and minutes.
Click OK.