ExcelInsider Team
Do you have numeric values in Excel like 1.75, 845, or 9.5 and want them to convert into readable hours and minutes? This situation often comes forth frequently in timesheets, billing systems, legacy datasets where time is recorded as either decimal hours or four-digit HHMM format.
To convert number to hours and minutes in Excel, follow these steps:
➤ Open up your datasheet.
➤ Use =TEXT(A1/24,”[h] “”hours,”” m “”minutes”””) in adjacent cells.
➤ You will get Hours and Minutes converted from Numbers.
In this article, we will show how to convert different types of numbers into hours and minutes in Excel. We’ll explain the process using methods like Divide by 24 and format as [h]:mm, TEXT function with [h] “hours,” m “minutes” Separate hours and minutes using INT() and MOD().
Understanding Numeric Time Format in Excel
In Excel, time is internally stored as a fraction of a day. So when we enter 1, it represents 24 hours. Decimal numbers like 1.5 mean 1 hour and 30 minutes. Excel does not recognize these as time values directly, which is why conversion becomes necessary. For example- 75 in decimal = 1 hour and 45 minutes.
Divide by 24 and Format Cell as Time [h]:mm
This is a simple method that will convert decimal numbers (like 8.5) into standard time format (like 8:30). This method will work well when you have work hours, travel durations, or machine operation times. They should be recorded as decimals.
Steps:
➤ Open your dataset which contains a column with decimal hour values. Here B2:B11.
![Divide by 24 and Format Cell as Time [h]:mm](https://excelinsider.com/wp-content/uploads/2025/06/2.convert-number-to-hours-and-minutes-in-excel-6.png)
➤ In cell C2 (or next to your first decimal value), enter the formula:
![Divide by 24 and Format Cell as Time [h]:mm](https://excelinsider.com/wp-content/uploads/2025/06/3.convert-number-to-hours-and-minutes-in-excel-10.png)
➤ Copy the formula down the column for all other entries. To do this, click the small square at the bottom-right corner of the cell and drag it down to cover the range (e.g., C2:C11).
![Divide by 24 and Format Cell as Time [h]:mm](https://excelinsider.com/wp-content/uploads/2025/06/4.convert-number-to-hours-and-minutes-in-excel-13.png)
➤ Now Select the range C2:C11.
➤ Right-click and choose Format Cells.
![Divide by 24 and Format Cell as Time [h]:mm](https://excelinsider.com/wp-content/uploads/2025/06/5.convert-number-to-hours-and-minutes-in-excel-3.png)
➤ Under the Number tab, select Custom.
➤ Enter [h]:mm in the Type box and click OK.
![Divide by 24 and Format Cell as Time [h]:mm](https://excelinsider.com/wp-content/uploads/2025/06/6.convert-number-to-hours-and-minutes-in-excel-12.png)
➤ You will see the converted numbers into hours and minutes.
Note:
➧ Why divide by 24? Excel stores 1 full day as 1. So, 1 hour = 1/24. Thus, any decimal value which represents hours must be divided by 24 to convert to Excel’s time format.
➧ [h]:mm is recommended instead of h:mm if the total exceeds 24 hours.
Use TEXT Function with hh and m
This method uses Excel’s TEXT function and converts decimal numbers into a readable time format. For example, like “3 hours, 45 minutes.”
It’s useful when you want to present time values clearly in reports, when printing or sharing with non-technical users. This method doesn’t change the actual value but formats the output as readable text.
Steps:
➤ Open up your excel sheet which contains your dataset. Here: B2:B11
➤ Click on the first empty cell in Column C (e.g., C2). Enter the following formula:
=TEXT(B2/24,”[h] “”hours,”” m “”minutes”””)
This formula converts the decimal time into a human-readable text format.
➤ Drag the formula down for the remaining cells in Column C (from C2 to C11).
Note:
➧ This method outputs text, not actual time values. So, the results in Column C cannot be used in further time-based calculations.
➧ The [h] format ensures hours are not reset after 24 hours (i.e., 25 hours shows as 25, not 1).
➧ No need to format cells using Format Cells > Custom, because the TEXT function already controls the output format.
Separate Hours and Minutes Using INT and MOD Functions
This INT and MOD Functions can break a decimal number into hours and minutes. It’s good for many situations where you need the time values split into separate numeric rather than combined or formatted as text.
For example, splitting time logs for reporting, payroll systems, or feeding time into automation systems. In this case, a value like 5.25 is split into 5 hours and 15 minutes.
Steps:
➤ Open up your excel worksheet where you have the dataset. Here: B2:B11
➤ Click on cell C2 (next to the first decimal value) and enter the following formula to extract hours:
=INT(B2)
This function will returns only the whole number portion of the decimal (i.e., the full hours).
➤ Now click on cell D2 and enter the formula to calculate minutes:
=MOD(B2,1)*60
This formula will calculates the fractional part (after the decimal) and then converts it into minutes by multiplying by 60.
➤ Drag both formulas (from C2 and D2) down to apply them to the rest of the rows (C3 to C11 and D3 to D11). Use the fill handle.
Note:
➧ The INT() function trims decimal places, so INT(5.75) gives 5.
➧ The MOD(number,1) function returns the decimal part. Multiplying it by 60 gives minutes.
➧ If minutes show long decimal values like 27.000001 due to floating-point precision, format the cells to 0 decimal places.
Frequently Asked Questions (FAQs)
What does dividing by 24 do in Excel time conversion?
If we divide a decimal number by 24, it tells Excel to interpret it as a fraction of a day.
How to convert whole number to hours and minutes in Excel?
Use =TEXT(A1/24, “[h] “”hours,”” m “”minutes”””). It will convert a whole number like 2 into 2 hours, 0 minutes.
How do you convert numbers to hours and minutes?
You can divide the number by 24 and format the cell as [h]:mm or use a formula like =INT(A1)&”h “&ROUND(MOD(A1,1)*60,0)&”m” for more control.
How do you show value as hours and minutes in Excel?
Select the cell, go to Format Cells → Custom → Type: [h]:mm. This will show the value in hours and minutes format.
How to convert number into hh mm in Excel?
Use the formula to get the hh:mm format-
=TEXT(A1/24,”hh:mm”) or =TIME(INT(A1), (A1-INT(A1))*60, 0)
Can Excel calculate hours and minutes?
Yes, Excel can calculate hours and minutes. It uses time functions like HOUR, MINUTE, or time arithmetic with TIME and formatted cells.
How to convert time to hours and minutes in Excel?
Multiply the time by 24 to get hours. For example,
=A1*24
Here, A1 contains a time value like 0.5 will give you 12.
Concluding Words
You can convert numbers into hours and minutes in Excel. It is very easy if you choose the right method for your dataset type.
Here’s a recap of the methods you can use:
- Divide by 24 and format as [h]:mm.
- TEXT function with [h] “hours,” m “minutes”
- Separate INT() and MOD() for hours and minutes
Additionally, identify your input type (decimal hours, HHMM, or Excel time) first. This single step will help you avoid errors and get the most effective formula right away. Let me know if you have any thoughts and queries.
