This is an overview:
Method 1 – Using the TEXT Function to Convert Decimals to Days Hours and Minutes in Excel
Step 1:
- Select a cell to see the output. Here, C5.
- Enter the formula.
=TEXT(B5/24, " d\d h\h m\m")- B5/24 is the decimal value and “ d\d h\h m\m” is the format_text.
- Press ENTER.
51 decimal values were converted to 30d 3h 30m.
Step 2:
- Drag down the Fill Handle to see the result in the rest of the cells.
Read More: How to Convert Decimal to Minutes and Seconds in Excel
Method 2 – Combining the INT and the TEXT Functions to Convert Decimals to Days Hours and Minutes in Excel
Step 1:
- Select C5.
- Enter the formula.
=INT(B5/24) & "days " & TEXT(B5/24, " h\h m\m")Formula Breakdown:
- B5/24 is the decimal value and the INT function converts it into days.
- B5/24 is the decimal value of the TEXT function and “ h\h m\m” is the format_text. The TEXT function converts it into hours and minutes.
- Press ENTER.
151 days 3h 30m is the output.
Step 2:
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 3 – Applying the Custom Format to Convert Decimals to Days Hours and Minutes in Excel
Steps:
- Select B5:B14.
- Copy this array pressing Ctrl + C.
- Paste the copied cells in column C pressing Ctrl + V.
- Press Ctrl + 1.
- In the Format Cells dialog box, select Number.
- Choose Custom.
- Enter h:mm in Type.
- Click OK.
Decimals were converted to hours and minutes.
Read More: How to Convert Decimal to Time Over 24 Hours in Excel
Download Practice Workbook
Download the practice workbook.
Related Articles
- Convert Number to Time hhmmss in Excel
- How to Convert 4 Digit Number to Time in Excel
- Convert Number to Military Time in Excel
- How to Convert Decimal to Time in Excel Using Formula
- How to Convert Number to Minutes in Excel
<< Go Back to Convert Number to Time | Time Conversion | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
