Table of Contents
Converting decimal time to hours and minutes in Excel is a simple process that can be done using the built-in functions. To convert decimal time to hours, the DECIMAL function can be used which takes the decimal value and multiplies it by 24. This will give the value in hours. To convert the remaining decimal value to minutes, the MOD function can be used which calculates the remainder after dividing by 1. The result can then be multiplied by 60 to get the value in minutes. By combining these functions, one can easily convert decimal time to hours and minutes in Excel.
Excel: Convert Decimal Time to Hours & Minutes
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")The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How do I convert decimal time to hours and minutes in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-convert-decimal-time-to-hours-and-minutes-in-excel/
stats writer. "How do I convert decimal time to hours and minutes in Excel?." PSYCHOLOGICAL SCALES, 25 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-convert-decimal-time-to-hours-and-minutes-in-excel/.
stats writer. "How do I convert decimal time to hours and minutes in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-convert-decimal-time-to-hours-and-minutes-in-excel/.
stats writer (2024) 'How do I convert decimal time to hours and minutes in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-convert-decimal-time-to-hours-and-minutes-in-excel/.
[1] stats writer, "How do I convert decimal time to hours and minutes in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I convert decimal time to hours and minutes in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.