If five entries of 7 hours 30 minutes add up to 13:30 in Excel, the total may be correct underneath. A clock-style display wraps after 24 hours. The intended duration is 37:30. Use an elapsed-hours format such as [h]:mm to show all the hours.
Build a numeric duration column
Enter five genuine time values of 7:30 in cells B2 through B6. In B7 enter =SUM(B2:B6). In desktop Excel, open Format Cells, select Custom and apply [h]:mm to B7. The brackets tell Excel to display accumulated hours instead of an hour-of-day component.
Formatting cannot repair text that only looks like a time. Check an imported cell with =ISNUMBER(B2). A FALSE result means that cell is not a numeric time value. Convert the source consistently before summing; do not silently replace unrecognized entries with zero.
| Representation | Result for five 7:30 shifts |
|---|---|
| Clock-style h:mm | 13:30 |
| Elapsed [h]:mm | 37:30 |
| Decimal hours using =B7*24 | 37.5 |
Separate stored values from display
Excel represents time as a fraction of a day. The five-shift total is 1.5625 days. Multiplying by 24 gives 37.5 hours. Format the decimal result as Number or General, not as a time. Otherwise Excel will interpret your new decimal-hour number as a count of days and display a confusing result.
Do not enter 7.30 when you mean 7:30. The numeric value 7.30 means 7.3 in a decimal-hours column, equivalent to 7 hours 18 minutes. A time column and a decimal-hours column need different entry conventions even when they describe the same work.
Subtract dates as well as times
For a task beginning at 09:00 on Monday and ending at 10:30 on Tuesday, enter full date-and-time values in A2 and B2. Use =B2-A2, formatted as [h]:mm, to display 25:30. This measures elapsed time, not working time after overnight closures or breaks.
Avoid using HOUR to extract the complete duration: it returns an hour component rather than all accumulated hours. Also avoid applying a modulo-one-day formula to tasks that can exceed 24 hours; it removes whole days from the result.
Validate before exporting
Compare the elapsed display with a decimal check. A 37:30 duration must equal 37.5 hours. If the destination expects decimal hours, export a dedicated numeric column rather than relying on how Excel formats a time cell. Keep the original duration column so you can reconcile the import afterward.
Microsoft documents elapsed totals in its add and subtract time guide. Formatting controls vary between desktop and web versions; if your interface cannot create the required custom format, use desktop Excel or export a clearly labeled decimal-hours column.
Frequently asked questions
Does changing the format change the hours?
No. It changes how a numeric value is shown. A text entry or incorrect source value still needs correction separately.
Can I show accumulated hours, minutes and seconds?
Use [h]:mm:ss where custom formatting is supported. Keep seconds through the calculation if they matter, and round only at the agreed reporting stage.
Related calculators and guides
- Decimal Hours Calculator & Bulk Timesheet Converter
- Convert Minutes to Decimal Hours in Excel: Formulas & Examples
- Convert a Timesheet Column to Decimal Hours Without Losing the Total
Explore more in Hours & Pay Calculators and Guides.