How to Convert Decimal Hours to Time (h:mm) in Excel

Decimal hours are easy to calculate with, but people read hours and minutes. When a payroll export, a project tracker or a formula gives you 7.75 or 8.33, you usually want to show 7:45 or 8:20. In Excel that takes one division and the right format, with a few traps around rounding and totals over 24 hours. For a single value, the decimal to time calculator does the conversion instantly.

Divide by 24 and apply a time format

Excel measures time in days, so a number of hours becomes a time value when you divide it by 24:

=A2/24

After entering the formula, select column B, press Ctrl+1 (Cmd+1 on a Mac), choose Custom and type [h]:mm. Without that step the cell shows a fraction such as 0.322917. With plain h:mm, the last row shows 17:15, because that format drops whole days; the brackets in [h]:mm show the full 41:15. Use [h]:mm:ss to see seconds.

Look at 8.33: it shows 8:19, not 8:20. 8.33 hours is exactly 8 hours 19 minutes 48 seconds, and a format without seconds hides the 48 seconds instead of rounding them. The rounding section below fixes that.

The result is a real time value, so you can sum it, compare it or add it to a start time.

Display decimal hours as h:mm text

When you need the time inside a sentence or label, TEXT converts and formats in one step:

=TEXT(A2/24,"[h]:mm")
="Total worked: "&TEXT(A2/24,"[h]:mm")

For 41.25, the second formula returns “Total worked: 41:15”. The output is text, so it is left-aligned and cannot be summed. Keep a numeric version in another cell if you need further arithmetic.

Split decimal hours into hours, minutes and seconds

To put the parts in separate columns:

=INT(A2)
=INT(ROUND(MOD(A2,1)*60,6))
=ROUND(MOD(A2*3600,60),0)

The ROUND(…,6) inside the minutes formula is not decoration. Decimals like 0.4 cannot be stored exactly in binary, so MOD(2.4,1)*60 can come out as 23.99999999, and a bare INT would return 23 minutes instead of 24. Rounding to six decimal places removes that noise without changing real values.

INT(A2) returns the full hour count even above 24 (41 for 41.25), which is one advantage of splitting the decimal instead of using HOUR on a time value.

Round to the nearest minute and avoid 7:60

A common homemade formula joins the hours and the rounded minutes:

=INT(A2)&":"&ROUND(MOD(A2,1)*60,0)

It fails in two ways, shown in column B: when the minutes round up to 60, and when the minutes have a single digit, because there is no leading zero.

The robust approach rounds the total minutes first and lets Excel handle the formatting:

=TEXT(ROUND(A2*60,0)/1440,"[h]:mm")
=ROUND(A2*60,0)/1440

The first formula returns text, as in column C. The second returns a numeric time value rounded to the whole minute; format it as [h]:mm and 8.33 displays as 8:20. To round to the nearest quarter hour instead, use =ROUND(A2*4,0)/96, which turns 8.33 into 8:15.

If you only need minutes padded to two digits, TEXT(…,"00") fixes the 7:5 problem but not 7:60. Rounding the total minutes first fixes both.

Durations over 24 hours

Weekly and pay-period totals routinely pass 24 hours. Three things to remember:

• Always format the result as [h]:mm. With h:mm, 41.25 shows as 17:15.
• Avoid TIME for the conversion. =TIME(A2,0,0) truncates the hours to a whole number (7.75 becomes 7:00) and wraps after 24 hours.
• If you want the days spelled out, combine INT and TEXT:

=INT(A2/24)&" d "&TEXT(MOD(A2,24)/24,"h:mm")

For 41.25 this returns “1 d 17:15”. To add up converted times afterward, see add hours and minutes in Excel.

Convert decimal minutes or seconds to time

The same idea works for other units: divide by the number of those units in a day, 1,440 minutes or 86,400 seconds.

=A2/1440
=A3/1440
=A4/86400

Some time clocks export hours and minutes with a decimal point, so 7.45 means 7 hours 45 minutes rather than 7.45 hours. Convert that style with:

=INT(A2)/24+ROUND(MOD(A2,1)*100,0)/1440

That returns 7:45 for 7.45. The minutes to hours converter is handy for checking minute totals by hand.

Negative decimal hours

A negative value, such as -1.5 for an employee 1.5 hours under schedule, cannot be displayed as a time in Excel’s default 1900 date system: =A2/24 shows #####. Build the sign yourself:

=IF(A2<0,"-","")&TEXT(ABS(A2)/24,"[h]:mm")

That returns -1:30 as text. The alternative, switching the workbook to the 1904 date system, shifts every existing date by 1,462 days, so reserve it for sheets without dates.

Google Sheets differences

The formulas above work unchanged in Google Sheets, including TEXT with [h]:mm. To format =A2/24 as a duration, choose Format → Number → Duration, which shows 41:15:00, or use Format → Number → Custom number format and enter [h]:mm. Sheets also displays negative durations with a minus sign, so the ##### workaround is only needed in Excel.

Common errors and fixes

• The result shows 0.322917. The cell is formatted as General. Apply [h]:mm.
• 41.25 shows as 17:15, or as a date like 1/1/1900. The format drops or misreads whole days. Use [h]:mm.
• 8.33 shows 8:19. Seconds are hidden, not rounded. Use =ROUND(A2*60,0)/1440.
• The text reads 7:60 or 7:5. A homemade text formula. Switch to =TEXT(ROUND(A2*60,0)/1440,"[h]:mm").
• #VALUE!. The decimal is stored as text, or written with a comma as the decimal separator. Convert it with =VALUE(A2) or =NUMBERVALUE(A2,",") first.
• TIME gives the wrong answer. It truncates fractions and wraps at 24 hours. Divide by 24 instead.
• #####. The value is negative or the column is too narrow.

To go the other direction, from 7:45 to 7.75, see convert time to decimal in Excel.