Excel guide · 3 min read
Convert time to decimal hours in Excel
=B2*24 turns 7:45 into 7.75 hours. Excel stores times as fractions of a day, so 24 converts days to hours. To go back, divide decimal hours by 24 and format the cell as h:mm (or [h]:mm for totals over 24 hours).Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Day | Hours (h:mm) | Rate | Decimal hours | Typed as | |
| 2 | Mon | 7:45 | 24.5 | 7.75 | 7.45 | |
| 3 | Tue | 8:20 | 24.5 | 8.33 | ||
| 4 | Wed | 6:50 | 24.5 | 6.4 | ||
| 5 | Thu | 9:05 | 24.5 | 9.1 | ||
| 6 | Fri | 8:10 | 24.5 | 11.25 | ||
| 7 | Sat | 4:35 | 24.5 |
Download this sheet as an Excel practice file (.xlsx)
Multiply by 24
A timesheet shows 7:45, but payroll and invoicing need 7.75. Behind the display, 7:45 is stored as 0.3229, the share of a 24-hour day it covers. Multiply by 24 and you get hours.
Format the result cell as Number or General. If it still shows a time like 18:00, the cell kept the time format from B2; change it with Home > Number Format > General.
Result: 7.75
7.75 hours. For minutes use *1440 instead: 465.
Round to 2 decimals (or to the quarter hour)
Some times do not convert cleanly. 8:20 is 8 and one third hours, 8.3333333333. Round to two decimals for display and invoices. If your policy rounds to the nearest quarter hour, multiply by 4, round, and divide by 4: =ROUND(B3*24*4,0)/4 gives 8.25.
Decide where you round. Rounding each day and adding the rounded figures can differ from rounding the weekly total: here the five rounded days add to 40.16, the rounded total is 40.17. Pick one rule and use it everywhere, usually the one your payroll provider uses.
Result: 8.33
8.33 hours for 8:20.
Totals over 24 hours
Add the times first, then multiply by 24. SUM(B2:B6) is 1.67 days; times 24 gives the weekly hours. This always works, whatever the cell format.
The trap is the display. A total formatted as h:mm wraps at 24 hours, so a 40-hour week shows as 16:10. The value is right, the format is not. Use the custom format [h]:mm for time totals, or convert to decimal and avoid the issue.
Result: 40.17
40.17 hours for Monday to Friday. =TEXT(SUM(B2:B6),"h:mm") shows a misleading 16:10; "[h]:mm" shows 40:10.
Pay: convert before multiplying by the rate
Multiplying a time straight by an hourly rate is the most expensive mistake on this page. =B2*C2 gives 7.91, because it pays 24.50 per day for 0.32 of a day. Convert to hours inside the formula, then multiply.
For the week, convert the total: =ROUND(SUM(B2:B6)*24*C2,2) gives 984.08.
Result: 189.88
189.88 for Monday: 7.75 hours at 24.50.
Decimal hours back to h:mm: divide by 24
Column E holds hours as decimals, the way many scheduling and billing tools export them. Divide by 24 to get a time value, then format as h:mm, or [h]:mm for totals. 6.4 hours becomes 6:24, not 6:40: the .4 is four tenths of an hour, which is 24 minutes.
Watch rounded decimals. 8.33 is 8 hours 19.8 minutes, so it shows as 8:19, not 8:20. Round to the whole minute first: =TEXT(ROUND(E3*60,0)/1440,"h:mm") shows 8:20.
Result: 6:24
6:24 for 6.4 hours. For a label, =INT(E4)&" h "&ROUND(MOD(E4,1)*60,0)&" min" gives "6 h 24 min".
Times stored as text
Saturday's 4:35 in B7 came in as text. SUM skips it without warning: SUM(B2:B7) gives exactly the same total as SUM(B2:B6). Multiplying does convert it, so =B7*24 returns about 4.58, but a total built on SUM is quietly short.
Convert the column once with TIMEVALUE in a helper column, or select it and use Data > Text to Columns > Finish to turn the text into real times. Then compare =COUNT(B2:B7) with the number of rows; if COUNT is lower, some cells are still text.
Result: 4.58
4.58 hours for the text 4:35.
Common mistakes
Multiplying a time by the hourly rate
Times are fractions of a day, so you pay 1/24 of the right amount. Use =time*24*rate.
Typing 7.45 to mean 7 hours 45 minutes
7.45 is 7 hours 27 minutes (=TEXT(F2/24,"h:mm") shows 7:27). Type 7:45 or 7.75 and keep one convention per column.
Totals that reset after 24 hours
The h:mm format wraps. Use [h]:mm, or multiply the total by 24 for decimal hours.
Text times left out of SUM
SUM ignores text with no error. Convert with TIMEVALUE or Text to Columns and check COUNT against the row count.
FAQ
How do I convert hours and minutes to decimal in Excel?
Multiply the time by 24: =B2*24. 7:45 becomes 7.75 and 8:20 becomes 8.33 once you round to two decimals.
How do I convert decimal hours back to hours and minutes?
Divide by 24 and format as h:mm: =E2/24 shows 7:45 for 7.75. Use [h]:mm if the value can pass 24 hours.
Why is my decimal hours result showing as a time?
The result cell kept a time format. Change it to General or Number with Home > Number Format.
What is 45 minutes as a decimal?
0.75 hours, because 45/60 = 0.75. In Excel, =TIME(0,45,0)*24 returns 0.75.
How do I round time to the nearest quarter hour in decimal?
Use =ROUND(B2*24*4,0)/4. 9:05 becomes 9 and 8:20 becomes 8.25.
Functions used
ROUNDRounds to digitsSUMAdds numbersTEXTNumber to formatted textTIMEVALUEText time to a real timeINTRounds down to integerMODRemainder after divisionTIMEBuilds a timeCOUNTCounts numbers
Related guides
- How to calculate hours worked (and overtime) in Excel
- Convert minutes to hours and minutes in Excel
- How to add hours and minutes to a time in Excel
- Round time to the nearest 15 minutes in Excel
- Convert seconds to hh:mm:ss in Excel
- How to average time and durations in Excel
- How to divide in Excel
Updated