To convert decimal hours to an Excel duration, divide by 24 and apply the custom number format [h]:mm. With 5.75 in A2, =A2/24 displays 5:45. The same method converts 27.5 hours to 27:30 without losing the full day.
Suppose a work log records hours as decimals, but you need to read them as hours and minutes. The .75 in 5.75 hours means three-quarters of an hour, which is 45 minutes. Dividing by 24 converts the entire amount into Excel's unit of time, a day.
Convert a column of decimal hours
Follow along on the Example sheet in the decimal-hours conversion workbook. It includes the elapsed-time formats and reverse conversions used below.
Keep the original decimal values in column A and put the converted durations in column B. Use Decimal hours in A1 and Duration in B1.
- Enter
5.75in A2,27.5in A3, and1.75in A4. - Enter this formula in B2 and copy it through B4.
=A2/24- Select B2:B4 and open Format Cells. On Windows, press Ctrl+1. On Mac, press Command+1.
- In the Number settings, select Custom and enter
[h]:mmin the format's Type box. - Apply the format.
You should see these results.
| Decimal hours in A | Formula in B | Display with [h]:mm |
|---|---|---|
| 5.75 | =A2/24 | 5:45 |
| 27.5 | =A3/24 | 27:30 |
| 1.75 | =A4/24 | 1:45 |
Before formatting, B2 may show approximately 0.2395833333. That is 5.75 hours expressed as a fraction of a day. The date and time calculation guide explains how Excel stores that value.
Apply the elapsed-time format to column B, where the values have been divided by 24. If you apply it directly to 5.75 in A2, Excel reads that as 5.75 days and displays 138:00.
Keep totals over 24 hours visible
The brackets around h tell Excel to display accumulated hours. Without them, the ordinary h:mm time format wraps the displayed hours after each complete day.
| Duration being displayed | With h:mm | With [h]:mm |
|---|---|---|
| 5.75 hours | 5:45 | 5:45 |
| 24 hours | 0:00 | 24:00 |
| 27.5 hours | 3:30 | 27:30 |
The underlying value still contains the complete duration even when the display wraps. Microsoft's custom number-format guidelines document [h]:mm for elapsed hours and minutes. Use [h]:mm:ss if your source includes seconds you want to display.
You can add the converted values normally. Enter =B2+B3 in B6 and apply [h]:mm to B6. The result is 33:15, the combined duration of 5.75 and 27.5 hours.
The TIME function constructs a clock time from hour, minute, and second inputs. It wraps hour inputs above 23 into a time within a day, so it won't preserve the full elapsed hours in this example. Dividing decimal hours by 24 keeps those complete days in the value.
These formatting steps use desktop Excel. Microsoft's guide to adding and subtracting time notes that Excel for the web can calculate durations over 24 hours but cannot apply a custom number format. Use desktop Excel to set up the elapsed-time display when needed.
Convert a duration back to decimal hours
To reverse the conversion, multiply the numeric Excel duration by 24. In C2, enter this formula and copy down.
=B2*24Format column C as General or Number. B2's 5:45 becomes 5.75, and B3's 27:30 becomes 27.5.
This formula expects an Excel time or duration stored in days. If the source cell contains a complete date and time, multiplying it by 24 also multiplies the date portion. For a log with start and end timestamps, first calculate the elapsed duration using the guide to hours between two dates and times.
Convert decimal hours to total minutes
Multiply decimal hours by 60 to get the total number of minutes. For 1.75 hours in A4, enter this in D4.
=A4*60The result is 105 minutes. One hour contributes 60 minutes, and the remaining 0.75 hour contributes 45 minutes. The 45-minute portion alone doesn't describe the full duration.
Keep the result formatted as General or Number. If you're starting from the converted duration in B4 instead, multiply by 1440, the number of minutes in a day.
=B4*1440That also returns 105. The conversion factor depends on the starting unit, so check the column heading before choosing the formula.
If 6:30 is stored as text
An imported field containing the text 6:30 needs conversion with the TIMEVALUE function before you treat it as a numeric Excel time. For that limited clock-time example in F2, this formula returns 6.5 decimal hours.
=TIMEVALUE(F2)*24Format the result as General or Number. If F2 already contains a numeric Excel time, use =F2*24 instead. The text-to-time troubleshooting guide shows how to check the stored type and handle spaces or imported time notation.
That text example is limited to a time within one day. For longer elapsed amounts such as 27.5 hours, keep the numeric hours and use the divide-by-24 calculation from the first section.