Convert Number to Hours and Minutes in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you want to convert number to hours and minutes in Excel, you have come to the right place. Here, we will walk you through 2 easy and quick methods to do the task effortlessly.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.


2 Methods to Convert Number to Hours and Minutes in Excel

The following table has the Days and Number columns. We will use the Number column of this table to convert number to hours and minutes in Excel. To do the task we will go through 2 quick methods. Here, we used Excel 365. You can use any available Excel version.

Convert Number to Hours and Minutes in Excel


1. Using TEXT Function to Convert Number to Hours and Minutes in Excel

In this method, we will use the TEXT function to convert number to hours and minutes in Excel.

We will go through the following simple steps:

  • First, we will type the following formula in cell D5.
=TEXT(C5/24,"[h] ""hours,"" m ""minutes""")

Formula Breakdown

  • TEXT(C5/24,”[h] “”hours,”” m “”minutes”””) → the TEXT function put in a formatting to a number and represent the number in a new way.
  • C5/24 → divides cell C5 by 24.
    • Output: 0.0833333333
    • Explanation: Since C5 has an integer part, this is counted as a day value. Therefore, we need to divide cell C5 by 24 to make it an hour value.
  • “[h] “”hours,”” m “”minutes””” → these are the Format Codes for the Text function. Here, we surround the hour, “h” inside a square bracket. This is because we want the values to surpass twenty-four hours. Without the square bracket, the hours will begin from zero for every twenty-four hours.
  • TEXT(0.083333333,”[h] “”hours,”” m “”minutes”””) → becomes
    • Output: 2 hours, 0 minutes
  • After that, press ENTER.
  • Then, you can see the result in cell D5.

Convert Number to Hours and Minutes in Excel

As a result, you can see the conversion of number to hours and minutes in the Hours and Minutes column.

Convert Number to Hours and Minutes in Excel

Read More: How to Convert Number to Minutes in Excel (2 Easy Methods)


2. Dividing and Formatting Number

In this method, we will divide the numbers of the Number column by 24. This is because the integer part of the Number column’s data shows values on the basis of a day. Therefore, dividing the numbers by 24 will convert the numbers into an hour basis. After that, we will set a Time format for the numbers. Hence, the number will get converted to hours and minutes.

Let us follow some easy steps to do the task:

Step-1: Diving Numbers by a Day (24 Hours)

In this step, we will divide the numbers of the Number column by 24.

  • First of all, we will type the following formula in cell D5.
=C5/24

This simply divides the cell C5 value by 24.

  • Afterward, press ENTER.

Hence, you can see the result in cell D5.

  • Next, we will drag down the formula with the Fill Handle tool.

Convert Number to Hours and Minutes in Excel

Therefore, you can see a complete Dividing by 24 column.

Step-2: Setting a Format

In this step, we will set a Time format for the numbers. Here, we want to show the formatted result in the Hours and Minutes column. Therefore, we have to copy the values of the Dividing by 24 column to the Hours and Minutes column.

  • First, we will select the cells D5:D9 of the Diving by 24 column.
  • After that, right-click and select Copy from the Context Menu.

Convert Number to Hours and Minutes in Excel

Next, we have to paste the copied numbers into the Hours and Minutes column. Here, we need Paste Special option for this.

  • Afterward, we will select the cells E5:E9 of the Hours and Minutes column.
  • Along with that, we will go to the Home tab >> select the Paste option.
  • Moreover, from Paste Values option >> select Values & Number Formatting.

As a result, you can see the numbers in the Hours and Minutes column.

Next, we will set the Time format for the numbers of the Hours and Minutes column.

  • To do so, we will select the values of the Hours and Minutes column.
  • Moreover, from the Home tab >> go to the Number group.
  • After that, click on the Number Format which is marked with a red color box. This will bring out a Format Cells dialog box.

You can also bring out the Format Cells dialog box by pressing CTRL+1.

Convert Number to Hours and Minutes in Excel

A Format Cells dialog box will appear.

  • At this point, go to the Number group.
  • Then, select Time from the Categoy.
  • Along with that, select a Type.

Here, there is a number of Types to show the Time, among these we selected 1:30 PM as the Type. this is because we only want to show hours and minutes.

You can see the Sample of how the Time will appear.

  • Afterward, click OK.

Therefore, you can see the conversion of number to hours and minutes in the Hours and Minutes column.

Read More: Format Hours and Minutes Not Time in Excel (with Quick Steps)


Practice Section

You can download the above Excel file to practice the explained methods.

Convert Number to Hours and Minutes in Excel


Conclusion

Here, we tried to show you 2 methods to convert number to hours and minutes in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website ExcelDemy to explore more.


Related Articles

Afia Kona

Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo