If you don’t apply the proper format while converting the decimal numbers to times, you will get a time ranging from 0 to 24 hours. However, in this article, you will learn 2 quick methods to convert a decimal number to time over 24 hours in Excel.
How to Convert Decimal to Time Over 24 Hours in Excel: 2 Methods
1. Using Simple Division to Convert Decimal to Time Over 24 Hours
Here, I will show you how to convert decimal times to times over 24 hours. We can convert decimal numbers to times simply by dividing a decimal number by 24. But it will return only another decimal fraction number.
To get time instead of decimal fraction numbers, we have to apply a custom time format in the cells first.
Now, follow the steps below to learn how to do that.
❶ First of all, select the cell range C5:C12.
❷ Then press CTRL + 1.
The Format Cells dialog box will pop up.
❸ Go to the Number tab.
❹ Select Custom.
❺ Now insert the following format code in the box Type.
[h]:mmHere, [h] will return time in hours over 24 hours where necessary. Then ‘mm’ will return times in minutes as usual.
❻ Then click OK.
So, the custom time format has been applied to the selected cell range.
❼ Now insert the following formula in cell C5.
=B5/24Here, cell B5 has decimal numbers. Then the divisor 24 converts decimal numbers to times in 24-hour formats.
❽ Press ENTER.
❾ Drag the Fill Handle icon from cell C5 to C12.
Now the applied formula will convert decimal numbers to times over 24 hours like this:
Read More: How to Convert Decimal to Minutes and Seconds in Excel
2. Using TEXT Function to Convert Decimal to Time Over 24 Hours
The second way to convert decimal numbers to time over 24 hours is to use the TEXT function.
For that,
❶ Insert the following formula in cell C5.
=TEXT(B5/24,"[h]:mm")Formula Breakdown
- Cell B5 contains the decimal numbers.
- Divisor 24 converts decimal numbers into time in 24-hour
- [h]:mm is the time format. If you don’t put the square brackets around ‘h’, it will return time from 0 to 24 but not over 24
❷ Then press ENTER.
❸ Drag the Fill Handle icon from cell C5 to C12.
Now the applied formula will convert decimal numbers to times over 24 hours like this:
Read More: How to Convert Decimal to Days Hours and Minutes in Excel
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 2 methods to convert decimal to time over 24 hours in Excel. You are recommended to download the practice workbook attached with this article and practice all the methods. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.
Related Articles
- How to Convert 4 Digit Number to Time in Excel
- How to Convert 5 Digit Number to Time in Excel
- Convert Number to Hours and Minutes in Excel
- How to Convert Number to Minutes in Excel
- Convert Number to Military Time in Excel
- How to Convert Fraction into Hours and Minutes in Excel
- How to Convert Decimal to Time in Excel Using Formula
