ExcelInsider Team
While working on time log reports or event data sheets in Excel, users deal with the raw minutes most of the time. This does not become harder to interpret, but also makes the calculation clumsier. That’s why we often prefer to convert minutes to hours and minutes in Excel. This is more readable and frequently used in time sheets, workout logs, and in other related calculations.
In order to convert minutes to hours and minutes in Excel, follow these steps –
➤ Create a separate column and name it appropriately.
➤ In the first cell of the column, write the following formula-
=A2/1440,
Where A2 is the cell containing the time in minutes.
➤ Press Enter, and the conversion will give a decimal value.
➤ Press Ctrl + 1 , which opens the Format Cells window, or right-click and select the formatting option.
➤ Choose Category as Custom and the Type to h:mm to format it to hours and minutes.
➤ Drag the cells or use the Fill Handle option to apply the same formula to the whole column.
In this article, we dive deeper into how you can actually convert minutes to hours and minutes in Excel. Starting from the basic formula of division and the TEXT function, you will also see the curated guides of INT and MOD, as well as the VBA methods. With formatting tips and real-life use cases, you can decide which method perfectly aligns with your goals.
Divide Minutes by 1440 and Apply Time Format in Excel
The division method simply divides the minutes by 1440. As 1 day is considered 1400 minutes, it is the main constant that divides the minutes into hours and minutes. However, the output comes in decimals unless formatted specifically for hours and minutes.
Steps:
➤ Create a column separately to store the values in hours and minutes format.
➤ In the first cell of the column, write the division formula –
=A2/1440
where A2 is the first cell of the column holding the time in minute format.
➤ Pressing Enter will give the decimal value for this.
➤ Select the cell and right-click or press Ctrl+1 to open the format cells window.
➤ Choose the Category to Custom, Type to h:mm, and click OK.
➤ This will format the decimal value to h:mm format.
➤ Drag the cells or use the Fill Handle to generate the same formula and formatting for the rest of the cells.
Note:
If your data contains minutes more than 24 hours, you can select the Type as [h]:mm in the formatting window. This ensures the hours do not reset after 24.
Show Minutes as Text-Based Time Using TEXT Function
This is an effective way to skip formatting in the cell. It can easily convert minutes to hours and minutes, and you don’t need to change the numeric format. As a result, the output is generated as text, eliminating the need for extra formatting in output cells.
Steps:
➤ Create a column to hold the values in h:mm format.
➤ Type the TEXT formula in the cell –
=TEXT(C2/1440, "[h]:mm")
where C2 is the cell containing the previous time in minutes, and the argument [h]:mm automatically formats it.
➤ Press Enter to generate the output.
➤ Use Fill Handle to apply the same formula to the rest of the cells in the column.
Note:
The output is generated in text format and can’t be used to do further mathematical operations.
Break Down Minutes into Hours and Minutes Combining INT and MOD Functions
If you want to separately process the hours and minutes, combining the INT and MOD functions can deal with that. This mainly comes in handy when you want more readable values with complete control over your single values.
Steps:
➤ Create a column for this new labelled data.
➤ Type the below formula in the first cell of the column-
=INT(C2/60)&” :"&MOD(C2,60)
where C2 is the cell containing the time in minutes. The INT part deals with the hour only, and the MOD part specifically works for the minute format.
➤ Press Enter to display the output in the required cell.
➤ Apply the same procedure to the rest of the cells by dragging the cells across the column or using the Fill Handle.
Note:
➨ The output of this formula is also generated as text or strings. No further calculation is possible.
➨ You can customize the output in other formats, like –
=INT(C2/60)&” hrs, “&MOD(C2,60)&” mins”
Apply VBA Code to Convert Minutes into Hours and Minutes
For a large range of databases, you can automate the workflow with VBA when you need frequent data conversion to hours and minutes. Just make a custom function in VBA Macros, and you can reuse it repeatedly. All you have to do is save it as a macro-enabled workbook.
Steps:
➤ Go to the Developer tab and click on Visual Basic.
➤ In the VBA editor, click on Insert -> Module option.
➤ This will launch a blank space for writing VBA scripts. Paste this code below-
Function ConvertToTime(mins As Double) As Double
ConvertToTime = mins / 1440
End Function
➤ Click on Save and ensure it is saved as a macro-enabled file.
➤ Close the VBA and return to the worksheet.
➤ Make a new column to hold the new time values.
➤ In your preferred cell, write the following formula –
=ConvertToTime(C2)
where C2 is the cell containing the time values and ConvertToTime is the name of the customized formula created in VBA.
➤ Press Enter, and this will give you a decimal value.
➤ Select the cell and press Ctrl + 1 . This would open the Format Cells window.
➤ Choose Custom as the Category and h:mm as the Type.
➤ This formats the output in the cell as h:mm. Drag the cells to fill the rest of the column with the same formula.
Note:
You can modify the VBA script to format the cells automatically in h: mm. However, this will not be numeric but in text format, as VBA is not able to format cells.
Frequently Asked Questions
How do I prevent Excel from showing AM/PM instead of hours?
If you don’t want to show the AM/PM format in your Excel, you need to change your cell formatting. Select the desired cell and press Ctrl + 1 to open the Format Cells window. Choose Custom and select h:mm:ss format instead of h:mm:ss AM/PM one.
Why is Excel displaying ##### after applying the formula?
Your Excel cells can display ##### (error) after applying a formula. This indicates your cell width is narrower to display the whole value, or you might have used the wrong logic in the formula.
Can I show only hours or only minutes in Excel?
You can show only hours or minutes in time logs while working in Excel. Change the formatting of your cells by [h] for only hours and [m] for only minutes. Press Ctrl + 1 to open the Format Cell window or right-click and choose the option.
How can I extract hours or minutes separately from a time value?
To extract hours or minutes separately from a time value, you can use a built-in Excel function. If your time is in h:mm format, you can use the HOUR() or MINUTES() function to access that.
How do I handle minutes that are greater than 1440 while converting to hours and minutes?
Minutes greater than 1440 indicate that the period is more than one day. Excel automatically resets after 24 hours. Thus, you can format your cells [h]:mm while working with minutes that are greater than one day.
Final Words
You can convert minutes to hours and minutes in Excel in several ways, depending on your needs. For numeric values that need more mathematical manipulations, it’s better to use the division method as it is for beginners. For more advanced control, you can have hands-on VBA Macros. On the other hand, if displaying the minutes in hours and minutes is your target for readability, TEXT or INT and MOD functions are your go-to.
Regardless of what you choose, make sure to go through the steps properly and download our workbooks. It can help you clean your data to make it more accessible and readable.
