To display a numeric value as decimal hours in Excel, apply a custom format such as 0.00" h" to the cell. If the cell contains an Excel time value such as 7:30, first convert it to hours with a formula such as =A2*24; a number format changes how a value looks, not what the value means.
What decimal hours mean in Excel
Decimal hours express a duration as a number of hours, including a fraction of an hour. For example, 7 hours and 30 minutes is 7.5 hours, while 7 hours and 15 minutes is 7.25 hours. This form is useful for timesheets, labor estimates, billing calculations, and charts that need hours as ordinary numeric values.
Excel stores a time as a fraction of a day. One full day is 1, noon is 0.5, and 6:00 a.m. is 0.25. Consequently, the time value 7:30 is stored as 7.5 divided by 24, or 0.3125. Applying a decimal number format directly to that time value would show approximately 0.31, not 7.50. Multiplying by 24 converts the stored fraction of a day into a number of hours.
This distinction is the key to choosing the right format: a custom number format can add a readable hours label or control decimal places, but it cannot convert a fraction of a day into an hour count by itself.
Convert a time value to decimal hours
If a cell already contains an Excel time or duration, multiply it by 24 in a separate cell. With a time value in A2—for example, 7:30—enter =A2*24 in another cell. The result is 7.5. Format that result as a number with the desired decimal places, or use a custom format to append an hours label.
For example, to calculate elapsed time between two clock times, with a start time in A2 and an end time in B2 on the same day, enter =(B2-A2)*24. If A2 is 9:15 a.m. and B2 is 5:45 p.m., the result is 8.5 hours. The calculation returns a number, so it can be added, averaged, or multiplied by a rate.
If a shift might cross midnight and the cells contain clock times only, use =MOD(B2-A2,1)*24, with the start in A2 and end in B2. For a shift from 10:00 p.m. to 6:00 a.m., this returns 8. If the cells contain full date-and-time values, use the straightforward difference instead: with the start date and time in A2 and the end date and time in B2, =(B2-A2)*24 returns the elapsed hours, including durations longer than one day.
Apply a custom number format
In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, open the Number tab, choose Custom, and type a format in the Type box. For a two-decimal result followed by a short label, enter 0.00" h", then select OK. A result of 8.5 displays as 8.50 h, while the underlying value remains the number 8.5.
You can also open Format Cells from the Home tab by using the dialog box launcher in the Number group, then choose Number > Custom. On Excel for Mac, Command+1 opens Format Cells; the custom format is entered in the same way. Excel for the web has fewer formatting controls than the desktop app, so if you cannot enter or edit the desired custom code there, open the workbook in the desktop version.
0.00" h"always shows two decimal places, such as 8.50 h.0.0" h"always shows one decimal place, such as 8.5 h.0.##" h"shows up to two decimal places, omitting trailing zeroes; for example, 8.5 displays as 8.5 h and 8.25 as 8.25 h.0.00shows two decimal places without a unit label. This can be preferable when a heading already identifies the column as hours.
The zero in these examples is a required digit placeholder: it displays a zero when there is no digit in that position. The decimal point separates the whole-number portion from the fractional portion. Text in quotation marks is displayed literally, so the quoted h is a label, not an instruction that converts the value.
Choose decimal hours or hours and minutes
Decimal hours are not always the clearest way to present a duration. Use them when calculations or downstream systems expect a numeric hour quantity. Use an hours-and-minutes display when people need to read clock-style durations quickly. For instance, 8.5 decimal hours and 8:30 hours describe the same duration, but they are different representations.
For a duration stored as an Excel time, the custom format [h]:mm displays total elapsed hours and minutes. The square brackets matter: ordinary h:mm displays a time of day and cycles after 24 hours, whereas [h]:mm can display cumulative durations such as 27:30. Neither format converts the value to decimal hours; both display an underlying fraction of a day in hour-and-minute form.
A useful choice depends on the next step in the workbook:
- For payroll or other calculations based on a numeric hour quantity, calculate decimal hours and keep the result numeric.
- For a duration report intended for people to scan, consider an elapsed-time format such as
[h]:mm. - For a chart, pivot table, or export, check what the receiving feature expects. A numeric value like 8.5 is generally easier to aggregate as decimal hours than text such as 8:30.
Rounding and precision
A display format can make a value look rounded without changing its stored precision. If the actual result is 8.4996, formatting it with 0.00" h" displays 8.50 h, but calculations still use 8.4996. That difference can matter when totals are calculated from many rows or when a displayed value is copied into another calculation.
If the calculation itself must be rounded to two decimal places, use the ROUND function. With a decimal-hour result in A2, enter =ROUND(A2,2) in another cell; if A2 contains 8.4996, the result is 8.5. Then apply 0.00" h" if you want it displayed as 8.50 h. Decide whether to round each time entry or only the final total according to the rules of the report or organization; those approaches can produce different totals.
Also distinguish decimal places from minutes. One tenth of an hour is six minutes, not ten minutes. A value of 8.25 hours means 8 hours plus 0.25 of an hour, which is 15 minutes. Multiplying the fractional part by 60 is how to interpret it as minutes.
Common problems and their causes
- A time shows as a small decimal. The cell still contains a fraction of a day, such as 0.3125 for 7:30. Multiply the time value by 24 before applying a decimal-hours format.
- A duration resets after 24 hours. The value may be formatted as a time of day with
h:mm. For a cumulative display, use[h]:mm; for a numeric hour count, convert the value to decimal hours. - The label appears but calculations are wrong. A format such as
0.00" h"does not perform a conversion. Check the cell’s formula or value, not just its appearance. - The result is stored as text. If an entry includes a typed unit, such as 8.5 hours, Excel may treat it as text rather than a number. Keep the value numeric and use a quoted suffix in the number format.
- A negative duration displays as hash marks. Excel’s default date system generally cannot display negative time values as ordinary clock durations. For calculations where a negative result is meaningful, a signed numeric result in decimal hours can be clearer than a time-of-day display.
To check whether a cell is numeric, select it and inspect the formula bar, or test it in a spare cell using =ISNUMBER(A2), with the value being checked in A2. TRUE means Excel recognizes that cell as a number; FALSE indicates that it is not numeric. This check can help identify imported times or manually typed unit labels that look right but do not calculate.
Keep the result useful for calculations
Appending a unit with a custom format is usually better than joining the value and the label into text. A number displayed as 8.50 h remains available for SUM, AVERAGE, comparisons, and further arithmetic. By contrast, text made by combining a number and a label is not an ordinary numeric quantity and may need conversion before it can be used in calculations.
Keep the source time and calculated decimal hours in separate columns when readers may need both views. For example, a timesheet can show a start time, an end time, an elapsed duration in [h]:mm, and a decimal-hours column for calculations. Clear headings such as Duration and Decimal hours prevent someone from mistaking 8:30 for the decimal value 8.30; 8:30 is 8.5 hours, not 8.3 hours.
Frequently Asked Questions
Can a custom number format convert time to decimal hours?
No. A number format changes the display only. Multiply a stored time value by 24, then apply a format such as 0.00" h" to show decimal hours.
What format displays 8.5 as 8.50 hours?
Use 0.00" h" in Format Cells > Number > Custom. The result remains numeric, and the h is a displayed label.
How do I show total hours above 24?
For an elapsed duration stored as an Excel time, use the custom format [h]:mm. It displays total hours and minutes rather than cycling at 24 hours. For a decimal total, convert the duration to hours by multiplying by 24.
Is 7:30 the same as 7.30 hours?
No. A duration of 7:30 is 7 hours and 30 minutes, or 7.5 decimal hours. The number 7.30 means 7 hours and 0.30 of an hour, which is 7 hours and 18 minutes.
Why does my formatted result still have extra precision?
Number formatting affects what is displayed, not the stored result. Use ROUND in the calculation if the value itself needs to be rounded, and choose a suitable number format for how it should appear.
Related
- Company Contact List Template for Excel
- Combine Excel Files into One: Methods and Best Practices
- How to Combine Excel Files: Methods and Common Issues
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Group Rows in Excel by Values: A Practical How-ToTo group rows in Excel by matching values, sort the list by the column you want to group,…
- Where Is Remove Duplicates in Google Sheets? Find ItIn Google Sheets in a web browser, select the cells you want to check, then choose Data >…
- Freeze Panes in Excel: Keep Rows and Columns VisibleTo lock rows or columns in place while you scroll in Excel for Microsoft 365 on Windows, select…
- Excel Slicer Colors: How to Change and Customize ThemTo change Excel slicer colors in Microsoft 365 for Windows, select the slicer, open the contextual Slicer tab,…
- Google Sheets: Combine Two Columns into OneYes. In Google Sheets, combine two columns row by row with a formula such as =A2&" "&B2, which…
- Excel Data Validation Simple List: Create and Manage ItTo make a simple drop-down list in Excel for Microsoft 365 on Windows, select the cells, choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.