Display an Excel Duration Total That Exceeds Twenty-Four Hours
A total showing 5:10 may be correct as a clock-style display but misleading when the intended meaning is 29 hours and 10 minutes. Before adding an extra day to the formula, check whether the underlying calculation is being displayed in the wrong form.
This guide uses desktop Excel for Windows. Keep a copy of the workbook and identify whether the inputs are durations, clock readings or complete date-and-time values. Do not mix those meanings just because all three can contain a colon.
Give the total an elapsed-hours format
In a disposable sheet, enter 18:20 in A2 and 10:50 in A3 as time values. Put =SUM(A2:A3) in A4. The intended total is 29 hours and 10 minutes.
Select A4 and open Home, Format, Format Cells. On the Number tab, choose Custom and enter [h]:mm;@ in Type. Microsoft documents this format for totals exceeding 24 hours. The bracketed hour component lets the display show the accumulated hours rather than cycling through a single day.
The expected displayed result is 29:10. Retain the SUM formula and check it in the formula bar; do not type the expected answer over the calculation.
Keep duration and decimal-hour outputs separate
An hours-and-minutes result is not a decimal number of hours. For example, thirty minutes is half an hour, not 0.30 hours. If another system requests decimal hours, confirm that requirement and build a separately labelled numeric output rather than relabelling a colon-formatted result.
Likewise, a calculation between a start and finish on different days needs the dates represented in its inputs. A display format cannot supply a missing start date or determine which day a clock reading belongs to.
Apply the accepted format to the real duration totals and verify one short total, one over 24 hours and an empty or incomplete record according to the workbook’s intended rule. Widen the column enough to read the complete result. Keep the units in the heading so the recipient knows whether 29:10 means elapsed hours and minutes or a different kind of value.
Sources: Microsoft adding and subtracting time.