You can use the following formula to convert a decimal time to hours and minutes in Excel:
=TEXT(B2/24, "h:mm") This will convert the decimal time in cell B2 into hours and minutes.
For example, if the decimal time in cell B2 is 2.5 then this formula will convert it to 2:30.
The following example shows how to use this formula in practice.
Example: Convert Decimal Time to Hours & Minutes in Excel
Suppose we have the following dataset in Excel that shows the time (in decimal format) that it took for various athletes to finish some task:
To convert each decimal time to hours and minutes, we can type the following formula into cell C2:
=TEXT(B2/24, "h:mm") We can then drag and fill this formula down to each remaining cell in column C:
Notice that each decimal time in column B has been converted to hours and minutes in column C.
For example:
- 2.5 hours is equal to 2 hours and 30 minutes.
- 3.05 hours is equal to 3 hours and 3 minutes.
- 4.25 hours is equal to 4 hours and 15 minutes.
- 5.5 hours is equal to 5 hours and 30 minutes.
And so on.
Note: If you would also like to display seconds, you can use the following formula:
=TEXT(B2/24, "h:mm:ss") Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Convert Time Duration to Minutes
Excel: Calculate Difference Between Two Times in Minutes
Excel: Calculate Difference Between Two Times in Hours