How to Sum Hours and Minutes in Excel

To sum hours and minutes in Excel, enter each duration as a time value (like 7:30), total them with =SUM(A1:A10), then format the result cell as [h]:mm — the brackets stop totals over 24 hours from rolling into days. 7:30 + 8:45 + 6:15 correctly shows 22:30. Flip between decimal hours and minutes anytime with our free minutes to hours converter.

The Direct Answer: =SUM() Plus the Right Format

Learning how to sum hours and minutes in Excel takes two moves, and most people only learn the first. Move 1: type your durations as time values — 7:30 means seven hours thirty minutes, 0:45 means forty-five minutes — and total them with =SUM(A1:A10). Move 2: format the total cell as [h]:mm (right-click > Format Cells > Custom > type [h]:mm). That second move is the whole secret. Excel stores times as fractions of a day — 12:00 noon is 0.5, 6:00 AM is 0.25 — so a plain h:mm format displays only the leftover after whole days are removed. Sum 20:00 + 8:30 and plain h:mm shows 4:30, silently discarding a full day. The bracketed [h]:mm format tells Excel to show the true elapsed hours — 28:30 — which is what a timesheet, a project log, or a payroll export actually needs. Get those two moves right and every other technique on this page is refinement.

Sum Time in Excel Over 24 Hours: The Bracket Fix Explained

The question of how to sum time in Excel over 24 hours deserves its own section because it is the single most common timesheet bug in existence. Here is exactly what happens: you sum a week's shifts — 8:00 + 8:30 + 7:45 + 8:15 + 9:00 = 41.5 hours — and the cell shows 17:30. Excel is not broken; it applied the h:mm clock format, which wraps at 24 like a clock face: 41:30 minus 24:00 = 17:30. The fix is the custom format [h]:mm, where the square brackets mean 'do not wrap.' Apply it and the same cell shows 41:30. Two related formats worth knowing: [m]:ss shows total minutes (90:00 for an hour and a half — handy for call-center logs), and [s] shows total seconds. The brackets work on any unit you put them around. One caution: once a cell is formatted [h]:mm, typing a plain number like 41 into it later will be read as 41 days — keep time entry in hh:mm form and let the format do the wrapping, not the typing.

Excel Sum Hours and Minutes Formula: SUM, TIME, and Helper Columns

The workhorse formula for an Excel sum of hours and minutes is plain =SUM() over a column of time values — no special function needed, because Excel already understands 7:30 as a number (0.3125 of a day). When you need to build a duration from separate hour and minute columns, use =TIME(B2,C2,0), which assembles hours in B2 and minutes in C2 into a real time value you can sum. To add a fixed block to every row — say a 30-minute lunch deduction — use =A2-TIME(0,30,0) in a helper column, then sum the helpers. For totals you want in decimal hours (payroll software wants 41.5, not 41:30), multiply the sum by 24 and format as General: =SUM(A1:A10)*24 gives 41.5 from 41:30, because one full day is 24 hours. Going the other direction — decimal 41.5 back to a time value — divide by 24: =41.5/24 formatted as [h]:mm. If your source data arrives as text like '7h 30m', convert first with =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(A2,"h ",":"),"m","")) before summing; SUM silently ignores text, which is why text-formatted times produce a suspicious 0:00 total.

How to Add Time in Excel Timesheet: A Worked Week

Here is how to add time in Excel on a real timesheet, start to finish. Column A holds the dates, B the clock-in times, C the clock-out times, and D the daily total with the formula =C2-B2 formatted as h:mm. A week might read: Mon 8:00–16:30 (8:30), Tue 8:15–17:00 (8:45), Wed 7:45–16:15 (8:30), Thu 8:00–18:00 (10:00), Fri 8:30–15:00 (6:30). The weekly total in D7 is =SUM(D2:D6) formatted as [h]:mm → 42:30. Overnight shifts need one adjustment: a shift from 22:00 to 6:00 computes as negative with plain subtraction, so use =IF(C2<B2, C2+1-B2, C2-B2) — the +1 adds a day when clock-out is 'earlier' than clock-in. Convert the 42:30 to pay with =D7*24*hourly_rate: at $25/hour, =42.5*25 = $1,062.50. That D7*24 step — time value times 24 equals decimal hours — is the bridge between Excel's day-fractions and payroll's decimal hours, and it is worth memorizing cold. Our decimal-hours payroll guide covers the payroll side in full, including FLSA rounding rules.

Subtracting Time and the Negative-Time Trap

Summing is only half the timesheet; the other half is elapsed time, and it has its own trap. =C2-B2 (end minus start) works until the result goes negative — Excel's default 1900 date system cannot display negative times and shows ##### instead. Three legitimate fixes: the IF formula above for overnight shifts (the common case), switching to the 1904 date system (File > Options > Advanced > 'Use 1904 date system' — allows negative time display but shifts all dates by 4 years, so avoid it in shared workbooks), or computing in decimal hours with =(C2-B2)*24, where negatives display fine as -2.5. A subtler trap: subtracting a break. The formula =C2-B2-TIME(0,30,0) deducts 30 minutes, but if the shift is shorter than the break the result goes negative and you are back to ##### — wrap it: =MAX(0, C2-B2-TIME(0,30,0)). When durations feed a payroll export, do the subtraction in one column, the decimal conversion in the next, and keep the raw clock times untouched — auditors and managers both want to see the originals.

Decimal Hours vs hh:mm: Converting Both Ways

Timesheets live in two notations and Excel straddles both, so conversions come up constantly. hh:mm → decimal: multiply the time value by 24 (=A2*24, General format) — 8:30 becomes 8.5, 7:45 becomes 7.75. Decimal → hh:mm: divide by 24 (=A2/24, [h]:mm format) — 8.5 becomes 8:30. The classic error is typing a decimal like 8.5 into a time-formatted cell: Excel reads it as 8.5 days and displays 204:00. Always convert with the /24 step, never by retyping. Quarter-hour anchors make mental checks fast: .25 = 15 min, .5 = 30 min, .75 = 45 min — if a decimal total ends in .83, that is 50 minutes (0.83 × 60), not 83. This is the same decimal-hours discipline our minutes to hours converter applies, and the payroll guide extends it to rounding compliance. One more bridge: Excel's date-time serial numbers — the integers behind dates — are cousins of Unix epoch timestamps, and our Unix timestamp converter decodes those when timesheet exports arrive as raw epoch values instead of readable dates.

When Excel Shows ##### or the Wrong Total

Four display problems cover nearly every 'my total is wrong' ticket. 1. #####: the column is too narrow or the result is negative — widen first, then check for a negative (see the subtraction section). 2. Total shows 0:00: your 'times' are actually text — SUM ignores text silently. Fix with TIMEVALUE or retype as real times; the green triangle in the cell corner is the tell. 3. Total wraps at 24 (41:30 shows as 17:30): the cell is formatted h:mm instead of [h]:mm — the bracket fix from section two. 4. Decimal total looks absurd (1020.00 instead of 42.5): you multiplied a time value by 24 twice, or formatted a decimal as time — =A2*24 needs General format, not [h]:mm. Diagnose in order: check the format, check for text, check for negatives, check for double conversion. Nine times out of ten the answer is one of the four, and the fix takes ten seconds once you know which.

Sum Any Timesheet Instantly — Free

The whole skill compresses to three habits: enter times as hh:mm, sum with =SUM(), and format totals as [h]:mm so nothing wraps at 24. Multiply by 24 when payroll wants decimals. For the conversions around the spreadsheet — minutes to decimal hours, decimal hours back to clock time — our free minutes to hours converter does them in one keystroke, and it lives on the ToolNest homepage with 130+ free converters. Pair it with the Unix timestamp in Excel guide when your data arrives as epoch values, and the freelance hourly-rate guide when those summed hours need a price tag.