Excel time totals over 24 hours: keep the full duration

Suppose a project log contains three durations: 8:30, 9:15 and 9:45. Together they represent 27 hours 30 minutes. A total that displays 3:30 looks clearly wrong, but the SUM formula may have included every entry. The cell may simply be displaying the result as a time of day.

The next report may want that total in a different form. A column expecting 27.5 hours needs a conversion, even after the original cell correctly displays 27:30. Starting with the same illustrative log makes it easier to follow which value changes. Records that cross midnight, and times copied from another tool, need a closer look before they can join the total.

Show all the hours in the total

In Excel time formats, h and hh display the hour within a day, from 0 through 23. Once a duration passes 24 hours, that display starts again. Bracketing the hour code changes the meaning: [h]:mm displays elapsed hours and minutes. This distinction is documented in Microsoft’s date and time formatting reference.

Assuming B2:B4 contain numeric Excel times, leave the SUM formula alone. In desktop Excel, select B5 and open Format Cells with Ctrl+1 on Windows or Command+1 on Mac. Under the number formatting options, choose Custom and enter [h]:mm as the format code. The total can then display 27:30.

This changes the presentation, not the stored value. Apply the same format to other duration totals so that a 27-hour project does not look shorter than a 23-hour project. If the cell displays hashes instead, widen the column first; a value that cannot fit is a separate issue from an incorrect calculation.

Convert the duration into reportable hours

A cell displaying 27:30 can still supply a numeric duration to another formula. Excel stores that duration as a fraction of a day: twelve hours is 0.5, while 27 hours 30 minutes is approximately 1.145833. Changing the total to General format exposes that underlying value. A report expecting a number of hours needs the equivalent value in hours.

To obtain decimal hours in C5, enter =B5*24 and give C5 a Number format. The result is 27.5. For a destination that expects minutes, =B5*1440 gives 1650. Microsoft uses these conversions in its time difference examples.

A decimal point cannot replace the colon. In 27.30 hours, the fraction .30 means 30 hundredths of an hour: 18 minutes. Thirty minutes is half an hour, which is why the correct decimal result is 27.5.

Check the incoming unit before applying a conversion. If the source already contains the number 27.5 measured in hours, multiplying it by 24 would be wrong. To turn that number into an Excel duration instead, divide it by 24 and format the result as [h]:mm. Keep the source column beside the converted column and label them “Duration (h:mm)” and “Decimal hours.” That makes the conversion understandable to the next person using the file.

Dates distinguish an overnight job from a longer one

The example above adds durations that have already been calculated. If your sheet begins with start and finish records, retain the dates as well as the clock times. A job that starts at 22:00 and ends at 06:30 might last 8 hours 30 minutes, or it might continue for another whole day.

Suppose D2 contains a genuine Excel date-time for October 27, 2026 at 22:00, and E2 contains October 28 at 06:30. Enter =E2-D2 in F2 and format F2 as [h]:mm. It represents 8:30. Change the end date to October 29, keeping 06:30, and the difference becomes 32:30. This follows Microsoft’s method for elapsed time between two dates and times.

Those calculations assume numeric date-time values. An imported string that looks like a date is not sufficient, and regional date conventions can affect how Excel reads it. Adding a date display format to a clock-only cell also cannot recover a job’s missing date.

The subtraction measures the entire interval. It does not know when someone took a break or left a process running unattended. If the report needs active work rather than elapsed time, record and account for those excluded intervals separately. A longer-than-expected result is a reason to compare the source records, not automatically discard a day.

What MOD removes from a time difference

Some older logs contain only clock times. If D2 is 22:00, E2 is 06:30 and you know the job lasted less than 24 hours, =MOD(E2-D2,1) gives a duration of 8:30 when appropriately formatted. It turns the negative clock difference into a positive remainder within one day.

The condition matters. MOD returns a remainder; it does not infer missing dates. Because one day is stored as 1, taking the remainder after division by 1 removes complete days. Applying it to a correct duration of 32:30 leaves only 8:30. Keep a straightforward subtraction when full start and end dates are available.

Matching clock times reveal another limit. A start of 09:00 and a finish of 09:00 could mean zero elapsed time or a finish exactly one day later. MOD returns zero for the clock-only calculation. You need the dates to decide what really happened.

The formula can also conceal an input mistake. An erroneous same-day finish of 08:00 after a 09:00 start becomes a plausible-looking 23-hour duration. Use the formula for records whose short overnight interval is known, rather than as a general repair for every negative result.

When a time-looking entry is text

If some rows remain absent from the total after you fix the format, inspect the imported data. The characters “08:30” can be text rather than a numeric time. SUM ignores text in referenced cells, so a range can look complete while its total omits some entries. Applying a time format alone does not convert those strings.

For a text clock value that Excel can interpret, such as “08:30” in B2, =TIMEVALUE(B2) can produce a numeric value in an adjacent column. Format that column as time and sum the converted values. Compare a few results with the original records before replacing anything.

TIMEVALUE supports times from 00:00:00 through 23:59:59 and ignores date information. It is therefore not a general parser for an elapsed-time string such as “27:30.” Using it on a full timestamp also loses the date needed for a multiday interval. Establish what the source system exports before choosing a conversion.

TEXT works in the opposite direction. =TEXT(B5,"[h]:mm") produces a display string. That can be useful inside a sentence, but SUM will ignore those strings if you total the resulting column. Microsoft recommends referring to the original number for subsequent calculations. Keep numeric durations and apply cell formatting when the values must remain available for arithmetic.

Finish the report in the format it needs

There is a platform limitation to the custom-format instructions. Microsoft’s add and subtract time guide, checked October 28, 2026, says Excel for the web can calculate times exceeding 24 hours but cannot apply custom number formats. The Format Cells procedure above is for desktop Excel.

If you are working in the browser and the destination accepts decimal hours, use a separate column that multiplies the duration by 24, formatted as Number. Send 27.5 with its unit explicitly identified as hours. If the destination specifically requires accumulated hours and minutes, use desktop Excel to set that custom format.

Before handing over the report, compare equivalent quantities: 27:30, 27.5 hours and 1650 minutes. If these agree but the total cell shows 3:30, review the display format. If the quantities disagree, check for rows outside the sum range, imported times stored as text, and start or end dates that differ from the source records.