Excel by default stores time in decimal format. This tutorial will demonstrate how to convert decimal format to time format in Excel using formulas. We’ll use the following sample dataset to illustrate our three methods to do so.
Method 1 – Using TEXT Function
The TEXT Function is mainly used to convert a numeric value into text in different formats.
Steps:
- In cell C5, enter the following formula:
=TEXT(B5/24, " d\d h\h m\m")
- Press Enter to return the result for the cell.
- Drag the Fill Handle down to apply the formula to the rest of the cells in the column.
The decimal times are converted into the specified time format.
Method 2 – Combining INT and TEXT Functions
The INT Function is mainly used to return the integer part of a decimal number.
Steps:
- In cell C5, enter the following formula:
=INT(B5/24) & "days" & TEXT(B5/24, " h\h m\m")
- Press Enter to return the result for the cell.
- Drag the Fill Handle down to apply the formula to the rest of the cells.
The decimal times are converted to the specified date format.
How Does the Formula Work?
- INT(B5/24) & “days”: Converts the decimal values into integers and then into days.
- TEXT(B5/24, ” h\h m\m”): Takes the values in decimal and converts them into hours and minutes.
Read More: Convert Number to Time hhmmss in Excel
Method3 – Manually Converting Decimal to Minutes and Seconds
We can also convert decimal to time in Excel manually by using a multiplication formula.
Steps:
- In cell C5, enter the following formula:
=B5*24*60
- Press Enter and drag the Fill Handle down to apply the formula to the rest of the cells.
The decimals are converted to Minutes.
- In cell D5, enter the following formula:
=B5*24*60*60
- Press Enter and drag the Fill Handle down to apply the formula to the rest of the cells.
The decimals are returned as Seconds.
Read More: Convert Number to Hours and Minutes in Excel
How to Convert Decimal to Time with a Custom Format
Excel has a custom format to transform decimals into time.
Steps:
- Select the data on which to apply the operation (in this case Column B) and press Ctrl+C.
- Press Ctrl+V in Column C.
- Select the cells of Column C and press Ctrl+1.
The Format Cells dialog box will open.
- Go to Number > Time > Type > Select desired type.
- Click OK.
The decimals are converted to time in the specified format.
Read More: How to Convert Decimal to Minutes and Seconds in Excel
How to Change Time to Decimal in Excel
We can also easily perform the reverse operation, namely converting time to decimal, by using the CONVERT function.
Steps:
- In cell C5, enter the following formula:
=CONVERT(B5,"day","mn")
- Press Enter and drag the Fill Handle down to apply the formula to the rest of the cells.
The decimal values are returned.
Read More: How to Convert Decimal to Time Over 24 Hours in Excel
Things to Remember
- Using the TEXT function is the easiest and most efficient method in all situations.
- If you don’t want to use a formula, use the Custom Format.
Download Practice Workbook
Related Articles
- How to Convert 4 Digit Number to Time in Excel
- How to Convert Number to Minutes in Excel
- Convert Number to Military Time in Excel
- How to Convert Decimal to Days Hours and 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!
