Afia Kona
If you want to convert number to hours and minutes in Excel, you have come to the right place. Here, we will walk you through 2 easy and quick methods to do the task effortlessly.
Download Practice Workbook
You can download the Excel file and practice while you are reading this article.
2 Methods to Convert Number to Hours and Minutes in Excel
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. To do the task we will go through 2 quick methods. Here, we used Excel 365. You can use any available Excel version.
1. Using TEXT Function to Convert Number to Hours and Minutes in Excel
In this method, we will use the TEXT function to convert number to hours and minutes in Excel.
We will go through the following simple steps:
- First, we will type the following formula in 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
- After that, press ENTER.
- Then, you can see the result in cell D5.
- At this point, we will drag down the formula with the Fill Handle tool.
As a result, you can see the conversion of number to hours and minutes in the Hours and Minutes column.
Read More: How to Convert Number to Minutes in Excel (2 Easy Methods)
2. Dividing and Formatting Number
In this method, we will divide the numbers of the Number column by 24. This is because the integer part of the Number column’s data shows values on the basis of a day. Therefore, dividing the numbers by 24 will convert the numbers into an hour basis. After that, we will set a Time format for the numbers. Hence, the number will get converted to hours and minutes.
Let us follow some easy steps to do the task:
Step-1: Diving Numbers by a Day (24 Hours)
In this step, we will divide the numbers of the Number column by 24.
- First of all, we will type the following formula in cell D5.
=C5/24This simply divides the cell C5 value by 24.
- Afterward, press ENTER.
Hence, you can see the result in cell D5.
- Next, we will drag down the formula with the Fill Handle tool.
Therefore, you can see a complete Dividing by 24 column.
Step-2: Setting a Format
In this step, we will set a Time format for the numbers. Here, we want to show the formatted result in the Hours and Minutes column. Therefore, we have to copy the values of the Dividing by 24 column to the Hours and Minutes column.
- First, we will select the cells D5:D9 of the Diving by 24 column.
- After that, right-click and select Copy from the Context Menu.
Next, we have to paste the copied numbers into the Hours and Minutes column. Here, we need Paste Special option for this.
- Afterward, we will select the cells E5:E9 of the Hours and Minutes column.
- Along with that, we will go to the Home tab >> select the Paste option.
- Moreover, from Paste Values option >> select Values & Number Formatting.
As a result, you can see the numbers in the Hours and Minutes column.
Next, we will set the Time format for the numbers of the Hours and Minutes column.
- To do so, we will select the values of the Hours and Minutes column.
- Moreover, from the Home tab >> go to the Number group.
- After that, click on the Number Format which is marked with a red color box. This will 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.
- At this point, go to the Number group.
- Then, select Time from the Categoy.
- Along with that, select a Type.
Here, there is a number of Types to show the Time, among these we selected 1:30 PM as the Type. this is because we only want to show hours and minutes.
You can see the Sample of how the Time will appear.
- Afterward, click OK.
Therefore, you can see the conversion of number to hours and minutes in the Hours and Minutes column.
Read More: Format Hours and Minutes Not Time in Excel (with Quick Steps)
Practice Section
You can download the above Excel file to practice the explained methods.
Conclusion
Here, we tried to show you 2 methods to convert number to hours and minutes in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website ExcelDemy to explore more.
Related Articles
- How to Convert Fraction into Hours and Minutes in Excel (3 Ways)
- Convert Number to Military Time in Excel (2 Easy Methods)
- How to Convert 4 Digit Number to Time in Excel (3 Methods)
- Convert Number to Time hhmmss in Excel (2 Suitable Ways)
- How to Convert 5 Digit Number to Time in Excel (4 Quick Tricks)
