Convert Decimal Hours to Time (hh:mm) in Excel

To convert decimal hours to time in Excel, divide the number by 24 and format the result as a time. So 7.75 hours becomes =A2/24, which shows 7:45 in the h:mm format. This guide is for anyone working with timesheets, project hours or exported reports, where 7.75 needs to read as 7 hours 45 minutes. It covers the number format, the TEXT function, totals over 24 hours and the reverse conversion.

How Excel stores time (and why you divide by 24)#

Excel stores time as a fraction of one day. The number 1 is a full day of 24 hours, so 0.5 is 12 hours and 0.25 is 6 hours. A time like 7:45 is really the number 0.322916…, formatted to look like a clock.

A decimal such as 7.75 means 7.75 hours. To turn hours into Excel's "fraction of a day", divide by the number of hours in a day:

The part after the decimal point is a share of an hour, not minutes. That is why 9.4 hours is 9:24 (0.4 × 60 = 24 minutes), not 9:40. The date basics lesson shows how dates use the same number system.

Convert decimal hours to hh:mm with a number format#

The best method keeps the result as a real number, so you can still add and subtract it. With decimal hours in A2:

1. In B2, type =A2/24 and press Enter.
2. Open Format Cells (Windows: Ctrl+1; Mac: ⌘1).
3. Choose Custom and type h:mm in the Type box, or hh:mm for a leading zero (07:45).
4. Click OK and fill the formula down.

B2 now shows 7:45, but the cell still holds 0.3229. That matters: you can sum the column, subtract it from a deadline, or compare it with another time. The guide to custom number formats explains the format codes in more detail.

Convert decimal hours to time text with TEXT#

The TEXT function converts the number and formats it in one step, returning text:

=TEXT(A2/24,"h:mm")

With 7.75 in A2, this returns 7:45 as text. It is handy when you want to join the time into a sentence:

="Worked "&TEXT(A2/24,"h:mm")&" hours"

That gives Worked 7:45 hours. The catch is that text can't be added up: =SUM() over TEXT results returns 0. Use TEXT for labels and reports, and the number-format method for anything you calculate with. The TEXT function guide and the article on TEXT function formats list the other codes. The TEXT and VALUE lesson shows how to switch between text and numbers.

Totals over 24 hours: use [h]:mm#

A normal h:mm format rolls over after 24 hours, just like a clock. If a week's hours total 30.25, =A2/24 formatted as h:mm shows 6:15: 1 full day is hidden and only the leftover 6 hours 15 minutes are shown.

Put the hours part in square brackets to show the full total:

=TEXT(30.25/24,"[h]:mm")

This returns 30:15. In Format Cells, the same custom format is [h]:mm. Use it for weekly totals, project hours and overtime. The time difference guide uses the same format when shifts add up past a day.

Convert decimal minutes or seconds to time#

Minutes and seconds work the same way. You just divide by a different number:

So a call log that records 135 minutes shows as 2:15 after =A2/1440.

You can also split the decimal yourself with INT and MOD. =INT(A2) gives the whole hours (7) and =MOD(A2,1)*60 gives the minutes (45). The article on MOD and INT explains both functions.

Round to the nearest minute first#

Decimals that don't divide evenly into minutes can show a surprise. 7.333 hours is 7 hours 19.98 minutes, so depending on the format it may display as 7:19 or 7:20. To be safe, round to whole minutes before you format:

=ROUND(A2*60,0)/1440

A2*60 turns hours into minutes, ROUND makes it a whole number (440), and /1440 turns the minutes into a time. Formatted as h:mm, it shows 7:20.

You may also see =TIME(INT(A2),MOD(A2,1)*60,0). It works for many values, but TIME drops any fraction of a minute and wraps around after 24 hours, so /24 with [h]:mm is the more reliable choice for totals.

Convert time back to decimal hours#

To go the other way, multiply by 24. With the time 7:45 in B2:

=B2*24

The result is 7.75. If it shows as a time such as 18:00, change the cell's format to General or Number. This is the conversion you need for pay: hours × hourly rate only works with decimal hours. The guide to adding hours to a time shows the same /24 and *24 steps in reverse.

Common mistakes#

These are the usual reasons a converted time looks wrong:

1. Formatting without dividing. Formatting 7.75 as h:mm shows 18:00, because Excel reads it as 7.75 days.
2. Reading .4 as 40 minutes. 9.4 hours is 9:24. The decimal is a share of an hour.
3. Totals that reset. Use [h]:mm for anything that can reach 24 hours.
4. Adding TEXT results. TEXT returns text, so totals come out as 0. Keep a number column for maths.
5. Negative times. In the default date system, a negative time shows as ##### instead of a value. Check the subtraction order.

Practise it#

Converting decimals to time is one formula, but it is easy to get wrong under pressure. Try the exercise on this page, then practise TEXT and VALUE in a live spreadsheet and revisit how Excel stores dates as numbers, the same idea behind time values. In both lessons you type the formula and it checks your answer instantly.