How to Add and Subtract Time in Excel

Excel can add and subtract time, but it trips people up in a few predictable places: totals that reset after 24 hours, times that cross midnight, and negative results that show up as a row of #### signs. This guide covers how to add time in Excel, how to subtract it, and how to fix each of those problems.

It's about general time math. If you're adding up hours worked for payroll, the same formulas apply, but pay rules like breaks and overtime are outside this guide.

How Excel stores time

Excel stores times as decimal fractions of a day. In that system 1 is a full day, 0.5 is 12 hours, and 0.25 is 6 hours, so 6:00 AM is stored as 0.25. Everything below follows from that: adding times adds fractions of a day, and the cell's number format decides how the result looks.

How to add time in Excel

Type each time as hours and minutes, such as 6:45 and 9:30, then add them with a plus sign or SUM. Microsoft's own example puts 6:45 in B2 and 9:30 in B3:

=B2+B3        returns 16:15
=SUM(B2:B3)   same result

Fix totals over 24 hours

Add 12:45 and 15:30 and Excel shows 4:15 instead of 28:15. The total is right, but the standard time format rolls over at 24 hours like a clock.

To show the full total, apply a custom format:

1. Select the total cell.
2. Go to Home, then Format, then Format Cells (or press Ctrl+1).
3. On the Number tab, choose Custom, and type [h]:mm;@ in the Type box.

The square brackets tell Excel to keep counting hours past 24, so the cell now shows 28:15. Use [h]:mm:ss if you need seconds. You can check any total against the timer calculator, which doesn't roll over: for this sum it shows 1 day 4 h 15 min, or 28.25 decimal hours.

How to subtract time in Excel

Put the start time in one cell and the end time in another, then subtract the start from the end:

=C2-B2

Format the result with the custom format h:mm to see hours and minutes. Type times with an AM or PM ("9:00 AM", or "9:00 a" and "5:30 p") so Excel knows which half of the day you mean.

To get the result in decimal hours, multiply by 24. Then format the cell as a plain number:

=(C2-B2)*24

Subtract times that cross midnight

If the start is 10:30 PM and the end is 6:45 AM, =C2-B2 gives a negative number, because 6:45 AM is a smaller fraction of the day than 10:30 PM. Wrap the subtraction in MOD to wrap it around the end of the day:

=MOD(C2-B2,1)

MOD returns the remainder after dividing by 1, a full day, and the result takes the sign of the divisor, so it always comes out positive. Here it returns 8:15, which matches what you get when you work out the hours between two times, overnight included. This only works for spans shorter than 24 hours. For longer spans, include the date with each time (see below).

Spans longer than a day

Enter a full date and time in both cells, such as 10/18/2026 9:00 PM, then subtract. Microsoft's example multiplies the difference by 24 to get hours:

=(B2-B1)*24

Here B1 is the start and B2 is the end, following Microsoft's example, so the cell references are in a different order from the sections above.

Or leave out the *24 and format the result as [h]:mm to see hours and minutes.

Negative times show ####

In the default date system, Excel can't display a negative time, so the cell fills with #### signs. You have two options:

• Show it as text. This formula puts a minus sign in front of the absolute value:

  =IF(C2<B2,"-","")&TEXT(ABS(C2-B2),"[h]:mm")

  The result is text, so you can't add it up later.
• Switch to the 1904 date system (File, Options, Advanced, "Use 1904 date system"). It can display negative times, but switching changes the existing dates in the workbook, so only use it in a new workbook.

Add hours to a clock time

To find an end time, add a duration to a start time. If A2 holds 10:30 PM and B2 holds the duration 7:30, then =A2+B2 returns 6:00 AM. To add a number of hours stored as a plain number, divide it by 24 first:

=A2+B2/24     where B2 is 7.5 (hours)

You can also use the TIME function, which turns hours, minutes, and seconds into the fraction of a day Excel works with. To add 30 minutes to the time in A2:

=A2+TIME(0,30,0)

TIME divides any hour value over 23 by 24 and keeps the remainder, so TIME(27,0,0) gives 3:00. For durations of a day or more, use the /24 method above instead.

Key points

• Add with + or SUM, and format totals with [h]:mm so they don't reset at 24 hours.
• Subtract end minus start. Use =MOD(end-start,1) when a span crosses midnight.
• Multiply by 24 for decimal hours.
• Add a set amount to a time with =A2+TIME(h,m,s), or divide a plain number of hours by 24.
• Negative times need a text formula or the 1904 date system.

For a quick answer without a spreadsheet, the timer calculator adds and subtracts up to 20 times at once, and our guide to adding and subtracting time by hand explains the carrying.

Sources

• Microsoft Support, "Add or subtract time in Excel": support.microsoft.com
• Microsoft Support, "MOD function": support.microsoft.com
• Microsoft Support, "TIME function": support.microsoft.com
• Microsoft Support, "Date systems in Excel" (the 1900 and 1904 date systems and where to switch them): support.microsoft.com
• Better Solutions, "Excel Times" (times as decimal fractions of a day): bettersolutions.com
• Microsoft Tech Community, "Excel: how to show a negative time calculation" (1904 date system): techcommunity.microsoft.com