In this article, we’ll learn how to convert a fraction into hours, and minutes in Excel effectively with appropriate illustrations. While working with Microsoft Excel, sometimes we need to convert a fraction into hours and minutes. We’ll use Excel’s built-in functions, such as the TEXT and INT functions, and custom formatting cells, to convert fractions into hours and minutes.
How to Convert Fraction into Hours and Minutes in Excel: 3 Suitable Ways
Let’s assume we have an Excel large worksheet that contains information about several fractions. The fractions are given in Column B. We will convert the fractions into hours and minutes by using the TEXT, INT functions, and Custom Format. Here’s an overview of the dataset for today’s task.
1. Use TEXT Function to Convert Fraction into Hours and Minutes in Excel
We will apply the TEXT function to convert a fraction into hours and minutes in Excel. This is an easy and time-saving task also. From our dataset, we will learn how to use the TEXT function to convert a fraction or decimal to minutes and seconds in Excel. Let’s follow the instructions below to convert a fraction into hours and minutes!
Step 1:
- First of all, we will select a cell where we will apply the TEXT function. We select cell C5 for the convenience of our work.
- Hence, write down the below TEXT function in that cell. The TEXT function in the formula bar is,
=TEXT(B5/24, "h\h m\m")- Where B5/24 is the decimal value and “h\h m\m” is the format_text.
- Further, simply press ENTER on your keyboard. As a result, you will be able to convert 3.91 fraction values to 3h 54m days, hours, and minutes by using the TEXT function.
Step 2:
- After that, AutoFill the TEXT function to the rest of the cells in column C which has been given in the below screenshot.
Read More: Convert Number to Hours and Minutes in Excel
2. Combine INT and TEXT Functions to Convert Fraction into Hours and Minutes in Excel
In this method, we will not only use the INT function but also use the TEXT function to convert a fraction into hours, and minutes. Let’s follow the instructions below to convert a fraction into hours and minutes!
Step 1:
- First, we will select cell C5.
- After that, type the below INT and TEXT functions in that cell. The INT and TEXT functions in the formula bar are,
=INT(B5/24) & "H " & TEXT(B5/24, “M/M”)Formula Breakdown:
- Inside the INT function, B5/24 is the fraction value and the INT function converts it into hours.
- Inside the TEXT function, B5/24 is the fraction value of the TEXT function, “M\M” is the format_text, and the TEXT function converts it into minutes.
- Hence, simply press ENTER on your keyboard.
- As a result, you will be able to get 151H 5M as the returns of the INT and the TEXT functions.
Step 2:
- Further, AutoFill the INT and TEXT functions to the rest of the cells in column C which has been given in the below screenshot.
Read More: Convert Number to Time hhmmss in Excel
3. Apply Custom Format to Convert Fraction into Hours and Minutes in Excel
Last but not least, we will apply the Custom Format command to convert a fraction into hours, and minutes in Excel. Let’s follow the instructions below to convert a fraction into hours and minutes!
Steps:
- First, select the cell array from B5 to B14.
- Then copy this array by using the keyboard shortcut Ctrl + C simultaneously.
- After that, paste the copying cells array in column C by using the keyboard shortcut Ctrl + V simultaneously.
- Hence, press Ctrl + 1 simultaneously on your keyboard.
- As a result, a Format Cells dialog box will appear in front of you. From the Format Cells dialog box, firstly, select the Number Secondly, select the Custom command. Thirdly, type the h:mm in the typing box. At last press the OK option.
- After completing the above process, you will be able to convert the fraction into hours, and minutes which has been given in the below screenshot.
Read More: How to Convert Number to Minutes in Excel
Things to Remember
👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data. On the other hand, the #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.
👉 You can press Ctrl + 1 simultaneously on your keyboard to pop up the Format Cells dialog box.
👉 To copy data, you can press Ctrl + C simultaneously. To paste data, you can press Ctrl + V simultaneously on your keyboard.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to convert decimals to days hours and minutes will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
Related Articles
- Convert Number to Military Time in Excel
- How to Convert 4 Digit Number to Time in Excel
- How to Convert 5 Digit Number to Time in Excel
- How to Convert Decimal to Time in Excel Using Formula
- How to Convert Decimal to Time Over 24 Hours in Excel
- How to Convert Decimal to Days Hours and Minutes in Excel
