How to Convert Decimal to Time Over 24 Hours in Excel (2 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

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]:mm

Here, [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.

Using Format cells to Convert Decimal to Time Over 24 Hours

❼ Now insert the following formula in cell C5.

=B5/24

Here, cell B5 has decimal numbers. Then the divisor 24 converts decimal numbers to times in 24-hour formats.

❽ Press ENTER.

Using formula to Convert Decimal to Time Over 24 Hours

❾ 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.

Using TEXT Function to Convert Decimal to Time Over 24 Hours

❸ 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:

Result of Using TEXT Function to Convert Decimal to Time Over 24 Hours

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo