Adding time in Excel looks simple until you press Enter and watch nine hours of work collapse into something that reads 1:00 AM. The spreadsheet did the math correctly. The display lied. Time values in Excel are actually fractions of a day — and that one detail explains nearly every weird result you have ever seen when totaling a timesheet, summing project hours, or building a schedule. Get the formatting right, and the formulas you already know just work.

This guide walks through every common way to add time: stacking hours and minutes inside a single cell, summing a column of durations, using the TIME function to build values from scratch, and pushing totals past 24 hours without the rollover bug.

You will also see how to add a fixed number of minutes to an existing time, subtract one time from another, and avoid the silent killer that ruins most timesheets — text that looks like time but is not actually time. By the end, you should be able to total a week of hours, add 45 minutes to a meeting start, or break a duration into its component parts, all without the spreadsheet fighting you.

Most readers land here after a formula returned the wrong number, so let us start with the why before the how. Excel stores 24 hours as the number 1. Six hours equals 0.25. Forty-five minutes is roughly 0.03125. Add two times together and Excel adds those decimals — but when the sum crosses 1.0, the display wraps because the default time format only shows the fractional part. That is the rollover. The [h]:mm format and a couple of helper functions solve it permanently, and you will see exactly how below.

The simplest case is adding two times stored in cells. Type 8:30 in A1, type 1:15 in A2, then put =A1+A2 in A3. You get 9:45. That works because both inputs already look like time, Excel parsed them as time when you typed them, and the default cell format on A3 inherits the time display.

Drop the same formula into a column that was previously formatted as Number, and you will see something like 0.40625 — the underlying decimal. Right-click the result, choose Format Cells, pick Time, and the decimal becomes 9:45 again. Nothing changed in the math. Only the mask changed.

SUM works the same way over a range. Highlight A1:A20, type =SUM(A1:A20) at the bottom, and Excel adds the underlying decimals. If your range totals less than 24 hours you will see a normal time. If it totals more, the display rolls over. A 30-hour week shows up as 6:00 because Excel quietly dropped the 1 representing the first full day.

That is the moment most timesheets break, and it has nothing to do with your formula. The fix is purely cosmetic — change the format from h:mm to [h]:mm. The square brackets tell Excel to keep counting hours instead of resetting at every full day.

Building a time from scratch — when you do not have a pre-formatted cell — uses the TIME function. The syntax is straightforward: =TIME(hour, minute, second). So =TIME(14, 30, 0) returns 2:30 PM. =TIME(0, 90, 0) returns 1:30 AM, because TIME automatically rolls extra minutes into hours.

That rollover behavior is actually useful when you are adding minutes to an existing time, because you can write =A1+TIME(0, 30, 0) and Excel will correctly produce the new time even if it crosses an hour boundary. Want to add 90 minutes to a 9:45 meeting? =A1+TIME(0, 90, 0) returns 11:15. No manual carrying.

The function has one catch. TIME caps its hour argument at 23. If you pass 25, it returns 1:00 AM, not a duration of 25 hours. That makes TIME perfect for clock times but wrong for raw durations longer than a day. For durations, build the value as a decimal — for example, =A1+(2/24) adds two hours, =A1+(30/1440) adds 30 minutes, and =A1+(45/86400) adds 45 seconds. The arithmetic looks awkward but matches how Excel actually stores time, so it never overflows.

Adding hours and minutes separately is common when payroll exports give you hours in one column and minutes in another. The cleanest approach is =TIME(hours_cell, minutes_cell, 0). If hours can exceed 23 — for example a 38-hour weekly total — TIME will not work. Instead use the arithmetic form: =hours_cell/24 + minutes_cell/1440.

That gives you a proper Excel time serial, and the [h]:mm format will display it correctly no matter how large the total grows. Many timesheets stack the two: one column shows the daily total via TIME for readability, and the weekly total at the bottom uses SUM combined with [h]:mm so a 40-hour week actually reads 40:00.

Subtracting times is just as important as adding them. A clock-in cell, a clock-out cell, and a formula like =B1-A1 produces the elapsed time worked. The result is itself a time value — format it as [h]:mm to keep it readable beyond 24 hours, which matters for overnight shifts.

If a shift starts at 10:00 PM and ends at 6:00 AM, a plain subtraction returns a negative number because the end time is numerically smaller. The fix is =MOD(B1-A1, 1) which wraps negative results back into the 24-hour cycle. For shifts that span multiple days, the more robust solution is to store full date-time stamps rather than time-only values, so the math handles the day change automatically.

The silent killer of every time formula is text that looks like time. When you paste data from a website, an email, or a CSV export, Excel sometimes stores 8:30 as the literal string 8:30 instead of as a time value. The cell looks identical to a real time. SUM ignores it. AVERAGE ignores it. Arithmetic returns errors. The tell is alignment — real time values right-align by default, text left-aligns. Click the cell and check the formula bar. If you see a leading apostrophe like '8:30, the value is text.

Fix text-as-time with TIMEVALUE, which converts a text string back into a real time number. =TIMEVALUE(A1) returns the proper decimal. Wrap a column with =TIMEVALUE(A1) in a helper column, copy the results, then paste back as values over the originals. For larger imports, Text-to-Columns with the Date column data format often converts the entire range in one step. Avoid this trap by formatting the destination cells as Time before pasting, which prompts Excel to interpret incoming strings as time values rather than text.

Conditional formatting plays nicely with time totals and is worth setting up on any timesheet you reuse. Highlight a column of daily totals, open Conditional Formatting, and add a rule for cells greater than =TIME(8, 0, 0) to flag overtime in red. Add another rule for cells less than =TIME(7, 0, 0) to flag under-hours in yellow.

Because both rules compare against real time values, they update automatically as you enter new data. Avoid hard-coded decimal thresholds like 0.333 — they work, but no one reading the spreadsheet later will understand what 0.333 means without a comment explaining that it equals eight hours.

Converting time to a decimal number is a routine need for billing, payroll calculations, or comparisons. Multiply any time value by 24 to get hours as a decimal — =A1*24 turns 7:30 into 7.5. Multiply by 1440 to get minutes — useful when you need to compare durations or run statistics. The reverse also works — =B1/24 turns the number 7.5 back into a time value of 7:30 once you format the cell as time. Many payroll exports give you decimal hours and expect decimal hours back, so this conversion is the bridge between human-readable time and machine-readable totals.

The NOW and TIME functions can be combined for live duration tracking. Put a fixed start time in A1, then write =NOW()-A1 in B1 with format [h]:mm to see elapsed time since the start. Press F9 to recalculate when you want a fresh value. This works for tracking how long a task has been running, monitoring meetings, or building a stopwatch — though for a true ticking display you need a small VBA loop, which is beyond the scope of plain formulas. For most purposes, manual recalculation is enough, and the formula updates whenever Excel recalculates the workbook anyway.

Rounding time follows the same rules as rounding numbers, with the conversion factor doing the work. To round to the nearest 15 minutes, use =MROUND(A1, TIME(0, 15, 0)). The MROUND function rounds to the nearest multiple of its second argument, and TIME builds that 15-minute value as a real time number. To round up to the next 15-minute boundary use CEILING with the same time argument, and FLOOR to round down. This is invaluable for billing systems that round to the nearest quarter hour, or scheduling tools that snap to defined increments without manual adjustment.

For Excel users building serious time-tracking spreadsheets, the combination of named ranges, structured tables, and [h]:mm formatting transforms a fragile timesheet into something colleagues can actually use.

Convert your data range into a table with Ctrl+T, name the columns Start, End, and Hours, and write the Hours column formula as =[@End]-[@Start]. Structured references read more clearly than B2-A2, and the table format automatically extends formulas to new rows as you add data. Weekly and monthly totals at the bottom — formatted as [h]:mm — give you accurate sums without the rollover bug and without rewriting formulas every time the range grows.

Finally, learning the shortcut Ctrl+Shift+Colon inserts the current time into the active cell as a static value. Ctrl+Semicolon does the same for the current date. Combined, they make manual time-tracking faster than reaching for a calculator. Pair them with a [h]:mm column for elapsed time, and you have a lightweight log that requires almost no setup. The same Ctrl+1 shortcut to open Format Cells, then Custom, then [h]:mm becomes muscle memory once you have rescued enough timesheets that quietly lost half their hours to the rollover bug.

Common edge cases round out the picture. A leading zero in a typed time — like 0:08:30 — confuses Excel and may be parsed as a number rather than a duration. Type 8:30 cleanly, then if you need it interpreted as 8 minutes 30 seconds, use the format mm:ss or build the value with =TIME(0, 8, 30). Negative time values display as ##### by default because Excel cannot render them in the standard time format.

Switching to the 1904 date system in Options allows negative times to display, but it shifts every other date in the workbook by four years, so most users prefer MOD-based workarounds instead. And when sharing files across regions, remember that time separators differ — Europe often uses commas, the US uses colons — so a formula that parses correctly on your machine may fail on a colleague's without an explicit format change.

The HOUR, MINUTE, and SECOND functions break a time value into its parts, which becomes useful when you want to do conditional logic against just one component. =HOUR(A1) returns the integer hours from a time, ignoring minutes and seconds. =MINUTE(A1) returns just the minutes. You can build statements like =IF(HOUR(A1)>=17, "Evening", "Day") to classify shifts, or use MINUTE to round bills to specific minute thresholds.

The three functions are the inverse of TIME — TIME builds a value from parts, HOUR and MINUTE and SECOND pull those parts back out. Combined they give you full control over how time data is parsed, formatted, and conditionally processed inside larger formulas.

Sharing workbooks that contain time math introduces one more wrinkle worth knowing about. When a workbook moves between machines with different regional settings, the time separator can shift — colons become dots, twelve-hour displays become twenty-four-hour, AM and PM disappear or reappear. The underlying values do not change because Excel stores them as decimals, but the displayed format can confuse the next person to open the file.

If a workbook is destined for international use, lock in a custom format like [h]:mm with the bracketed format rather than relying on regional defaults, and consider documenting the time conventions in a notes tab so collaborators understand what they are looking at without guessing.

Excel's IFERROR function pairs well with time formulas that occasionally fail on bad data. Wrap a time subtraction like =IFERROR(B1-A1, 0) so empty rows or text cells return zero instead of #VALUE! errors that cascade through SUM totals. This matters most on templates that get filled in over days or weeks — a partially-completed timesheet should still sum correctly without erroring out on the empty rows. For more sophisticated handling, =IF(AND(ISNUMBER(A1), ISNUMBER(B1)), B1-A1, "") returns blank when either input is missing, keeping totals clean and avoiding visual noise from zero entries.

One last technique worth picking up — array-style summing across multiple columns. If your timesheet stacks start times in column A and end times in column B for fourteen rows, =SUMPRODUCT(B1:B14-A1:A14) returns the total elapsed hours across the entire block without needing a helper column for daily duration. Format the result as [h]:mm and you have a weekly total in a single formula.

SUMPRODUCT handles the row-by-row subtraction internally, then sums the differences. Combined with conditional ranges — like SUMPRODUCT with multiplication by a logical test — you can sum only the days that meet specific criteria, building reports that aggregate just the overtime, just the night shifts, or just the billable client work.