Excel Sum of Hours Over 24: Show Elapsed Time Without Wrapping
Three work sessions can total 27 hours while a clock-style cell displays only 3:00. The total may be correct underneath; the display has wrapped after one day. Inspect the stored values before changing the calculation.
Construct a numeric duration fixture
Use desktop Excel for Windows for custom-format editing. Place the sample in A1:B4, but do not assume every CSV importer recognizes the text durations as time values. To create a controlled numeric fixture, enter =TIME(12,30,0), =TIME(8,45,0), and =TIME(5,45,0) in B2:B4.
Put total in A6 and =SUM(B2:B4) in B6. =COUNT(B2:B4) should return 3. If it returns fewer, inspect the source types rather than merely changing their number format. Preserve the original duration strings in a separate area for comparison.
Show elapsed hours instead of clock hours
Select B2:B6, open Format Cells, choose Custom, and enter [h]:mm. The bracketed hours allow complete elapsed hours to display. B6 should now show 27:00. A conventional clock display such as h:mm can show only the remainder within a day.
Change B6 temporarily to General and inspect 1.125. That is 27 divided by 24, because the underlying unit is days. Restore [h]:mm after this check; do not overwrite the value with the text 27:00 just to make the sheet look correct.
![One total can have two displays: Underlying value: 1.125 days; Clock format: 3:00 → Elapsed format [h]:mm; 27:00](/images/guides/excel-sum-hours-over-24-2-mobile.svg)
Convert the numeric total to decimal hours
Enter =B6*24 in D6 and use a normal numeric format. Expect 27. For S02 alone, =B3*24 gives 8.75. The decimal .75 means three quarters of an hour, not 75 minutes. A value of 8.45 decimal hours would describe something different.
Keep the numeric duration for sums and the decimal-hours column for systems requiring that unit. Include the unit in each header so an export does not mix day fractions, hours, and display strings.
B6: =SUM(B2:B4)
D6: =B6*24
D3: =B3*24
F2: =COUNT(B2:B4)Handle midnight with an explicit rule
For a single overnight session known to last less than 24 hours, 22:30 to 02:15 lasts 3:45. A time-only formula =MOD(end-start,1) can express that rule. It also makes an accidentally reversed pair look like a long positive duration, so investigate unexpected results.
For multi-day work, include actual start and end dates and use end minus start. For example, September 18 at 22:30 through September 20 at 02:15 is 27:45. Applying MOD(...,1) would drop a whole day and return only 3:45.
Retain dates when the interval can exceed a day
Build the multi-day control using DATE and TIME functions below. Format its result as [h]:mm and compare 27:45. Multiplying it by 24 gives 27.75 hours. A negative result requires reviewing the timestamps; do not use ABS merely to hide reversed inputs.
For imported timestamps, first confirm both cells are numeric Excel date-times using ISNUMBER. These arithmetic examples do not calculate time-zone or daylight-saving transitions; normalize such timestamps using an explicit rule before subtraction.
=(DATE(2026,9,20)+TIME(2,15,0))-(DATE(2026,9,18)+TIME(22,30,0))
Check the receiving file format
A CSV does not retain Excel custom formatting rules. Export an explicitly labeled decimal_hours value or a deliberately prepared text duration according to the receiver's specification, then re-open the export and compare it.
In native desktop Excel 16.0 build 5569, the TIME/SUM fixture returned 1.125 days. Applying [h]:mm displayed 27:00; h:mm displayed 3:00, and multiplying the total by 24 returned 27. An input change recalculated to 28 hours, and restoring the fixture and reopening the saved workbook preserved 27:00. The multi-day control displayed 27:45 and 27.75 hours; MOD reduced it to 3:45. These tests used numeric formulas, not automatic CSV duration parsing.
| Checkpoint | Expected value |
|---|---|
| SUM of the three numeric durations | 1.125 days |
| Total displayed with [h]:mm | 27:00 |
| Same total displayed with h:mm | 3:00 |
| Total multiplied by 24 | 27 decimal hours |
Common questions
Why does 27 hours show as 3?
A clock-style display wraps at a day. Check the numeric total and apply [h]:mm for elapsed hours.
Is 8:45 the same as 8.45 hours?
No. 8:45 is 8.75 decimal hours.
Can MOD handle every overnight period?
It removes whole days, so use it only with an explicit under-24-hour rule. Use full timestamps for longer periods.
Sources & method
Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.