To subtract time in Excel, put the start time and end time in their own cells and type =end-start, for example =C2-B2. Then give the result a time format such as h:mm so it reads as hours and minutes instead of a decimal. The steps below apply to Microsoft 365 and Excel 2021 and 2024 on Windows and Mac, and they also cover times that cross midnight, totals over 24 hours and subtracting a fixed amount of time.
What Excel is really doing when it subtracts time
Excel does not store 3:30 PM as the words “3:30 PM.” It stores every time as a fraction of a day. Noon is 0.5, 6:00 PM is 0.75, and one hour is 1/24 of a day (about 0.041667). A full date is a serial number, a count of days since a starting date, with the time of day sitting after the decimal point. Because times are just numbers, subtracting one from another is ordinary subtraction. The only extra job is telling Excel how to display the answer.
Keep these conversions handy, because most time formulas lean on them:
| Unit | Share of one day | Multiply a time difference by |
|---|---|---|
| 1 hour | 1/24 | 24 |
| 1 minute | 1/1440 | 1440 |
| 1 second | 1/86400 | 86400 |
Before you start
- Type times with a colon. Enter
8:30,17:15or5:15 p. If you use a 12-hour clock, add a space and an “a” or “p” so Excel knows whether the time is AM or PM. Without it,5:15is read as 5:15 in the morning. - Make sure the cells hold real times, not text. In a spare cell, type
=ISNUMBER(B2). TRUE means Excel recognized B2 as a number (a time is a number). FALSE means it is text, and the subtraction will fail. - Decide what you want back. A clock-style answer (8:45), a decimal number of hours (8.75) or a count of minutes (525) each need a slightly different last step.
If you are new to Excel formulas in general, the basics of the minus sign and cell references are covered in this beginner walkthrough of subtraction in Excel.
Subtract a start time from an end time
This is the method to use for timesheets, call logs and anything else where both times fall on the same day. The formula and the menus are the same in Excel for Windows and Excel for Mac.
- Type the start time in B2 (for example
8:30 a) and the end time in C2 (for example5:15 p). - Click D2, type
=C2-B2and press Enter. Always put the later time first. Excel may show something like8:45 AM, which is the right amount wearing the wrong label. - With D2 still selected, go to the Home tab, open the Number Format drop-down in the Number group (it often says General or Time), and choose More Number Formats. On Windows you can press Ctrl+1 instead.
- In the Format Cells dialog, pick Custom in the Category list, type
h:mmin the Type box and click OK. D2 now shows8:45, meaning 8 hours and 45 minutes. - To repeat the calculation for more rows, drag the fill handle (the small square at the bottom-right corner of D2) down the column. On Windows you can also select D2 and the cells below it and press Ctrl+D.
The same idea works down entire columns of start and end times. If you want to see that pattern applied to other kinds of numbers, see subtracting one column from another.
Pick a display format for the result
The format changes only how the answer looks. The value underneath stays the same, so you can switch formats at any time without breaking anything.
| Custom format code | Shows a 31-hour, 5-minute difference as | Best for |
|---|---|---|
h:mm | 7:05 | Gaps under 24 hours |
h:mm:ss | 7:05:00 | Gaps under 24 hours that need seconds |
[h]:mm | 31:05 | Weekly totals and anything that can pass 24 hours |
[mm]:ss | 1865:00 | Total minutes and seconds |
d "days" h:mm | 1 days 7:05 | Durations of a day or more (short of a month) |
Notice what happens in the first row. A plain h code rolls over every 24 hours like a clock, so 31 hours shows as 7. Square brackets around the code, as in [h], tell Excel to keep counting past 24. If any of your differences or totals can exceed a day, use [h]:mm. The full list of codes is on Microsoft’s number format codes page.
Turn the difference into hours, minutes or seconds
Payroll and billing usually need a plain number such as 8.75 hours, not a clock reading. Since the raw difference is a fraction of a day, multiply it by the matching unit.
- In an empty cell, type one of these, using your own cell addresses:
- Decimal hours:
=(C2-B2)*24 - Whole hours only:
=INT((C2-B2)*24) - Total minutes:
=(C2-B2)*1440 - Total seconds:
=(C2-B2)*86400
- Decimal hours:
- Press Enter.
- If the answer appears as a time (such as
6:00 PM) instead of a number, set the cell’s Number Format drop-down to General or Number.
The parentheses matter. =C2-B2*24 multiplies only B2, so the answer is nonsense. INT rounds down, so 8 hours 59 minutes still counts as 8 whole hours; use it only when partial hours should be dropped.
Pull out just the hour, minute or second part
=HOUR(C2-B2), =MINUTE(C2-B2) and =SECOND(C2-B2) return one piece of the difference as a whole number. For an 8:45 gap, HOUR returns 8 and MINUTE returns 45. These are clock parts, not totals: HOUR never returns more than 23, and MINUTE and SECOND never return more than 59. For a 31-hour gap, HOUR returns 7. Use the multiply-by-24 method when you need the total.
When the end time is after midnight
A night shift from 10:00 PM to 6:30 AM breaks the basic formula. Without dates, Excel sees 6:30 AM as smaller than 10:00 PM, so =C2-B2 comes out negative. In the default date system, a negative time shows as a row of pound signs (#####). There are three fixes, listed from the one to try first.
Option 1: wrap the subtraction in MOD
- In D2, type
=MOD(C2-B2,1)and press Enter. - Format D2 with
h:mm. For 10:00 PM to 6:30 AM it shows8:30.
MOD returns the remainder after division, and its result takes the sign of the divisor. Dividing by 1 keeps only the fractional part of the day, and because the divisor is positive the answer is never negative. Same-day shifts still give the correct answer, so you can use this one formula for a whole column of mixed day and night shifts. The details are on the MOD function reference.
Option 2: add a day with IF
=IF(C2<B2,C2+1,C2)-B2 adds one full day to the end time whenever it is earlier than the start time. It gives the same answers as the MOD version and some people find the logic easier to read later.
Option 3: store the date with the time
Both tricks above assume the gap is shorter than 24 hours. If a job can run for more than a day, type the full date and time in each cell, such as 11/3/2026 10:00 PM and 11/5/2026 6:30 AM. Then plain =C2-B2 works, and you can show the answer with [h]:mm (32:30) or turn it into hours with =(C2-B2)*24 (32.5). For gaps measured mostly in days, subtracting dates in Excel covers the date side in more depth.
Take a set amount of time off a time
Sometimes you are not comparing two times but removing a fixed amount, like a 30-minute lunch or a two-hour time zone shift. There are two good ways to write it.
Use the TIME function for amounts under a day
TIME(hour, minute, second) builds a time from three numbers. To take 1 hour 30 minutes off the time in A2, type =A2-TIME(1,30,0). To take off 45 seconds, use =A2-TIME(0,0,45). Keep the argument order straight: hours, then minutes, then seconds.
There is a catch. The TIME function always returns a value between 0 and 0.99988426, which is 0:00:00 to 23:59:59. Hours above 23 are divided by 24 and only the remainder is kept, so TIME(27,0,0) is the same as TIME(3,0,0). If you try =A2-TIME(27,0,0), Excel quietly subtracts 3 hours, not 27. Minutes and seconds above 59 are converted into larger units first (750 minutes becomes 12 hours 30 minutes), but the result still cannot reach a full day.
Divide by 24, 1440 or 86400 for any amount
To subtract an amount of any size, convert it to a fraction of a day yourself:
- Hours:
=A2-27/24, or=A2-F2/24with the number of hours in F2 - Minutes:
=A2-90/1440 - Seconds:
=A2-5000/86400
This version has no 24-hour ceiling. If A2 holds a date and time, the result moves back across days correctly. If A2 holds only a time and the answer would fall before midnight, wrap it as =MOD(A2-F2/24,1) to get the clock time on the previous day instead of pound signs.
Measure time elapsed up to right now
=NOW()-B2 subtracts the start in B2 from the current date and time. Two cautions apply. First, NOW includes today’s date, so B2 needs a date too; a bare time like 9:00 is treated as a time on Excel’s starting date, which makes the result more than a century long. Second, the value does not tick like a clock. It refreshes only when the worksheet recalculates, such as when you edit a cell or reopen the file. Format the result with [h]:mm or d "days" h:mm if it can exceed a day.
To stamp the current time into a cell as a fixed value that never changes, press Ctrl+Shift+: (Ctrl, Shift and the colon key) on Windows.
Worked example: a three-person shift log
Here is a small timesheet for Alex, Jordan and Priya. Priya works overnight, and each person takes an unpaid break. Column B holds the start, C the end, D the break length (typed as 0:45, 1:00 and so on), E the time worked and F the same value as decimal hours.
| Row | A: Name | B: Start | C: End | D: Break | E: Worked ([h]:mm) | F: Hours |
|---|---|---|---|---|---|---|
| 2 | Alex | 8:30 AM | 5:15 PM | 0:45 | 8:00 | 8.00 |
| 3 | Jordan | 9:00 AM | 6:20 PM | 1:00 | 8:20 | 8.33 |
| 4 | Priya | 10:00 PM | 6:30 AM | 0:30 | 8:00 | 8.00 |
| 5 | Total | 24:20 | 24.33 |
The formulas behind it:
E2: =MOD(C2-B2,1)-D2 (fill down to E4, format [h]:mm)
F2: =E2*24 (fill down to F4, format Number, 2 decimals)
E5: =SUM(E2:E4) (format [h]:mm)
F5: =SUM(F2:F4)
Three things are worth noticing. MOD lets Priya’s overnight row use the same formula as the day shifts. Subtracting D2 removes the break in the same step, because a break typed as 0:45 is also a fraction of a day. And if E5 were formatted as h:mm instead of [h]:mm, the total would read 0:20, because 24 hours 20 minutes rolls over to 20 minutes past a new day. The decimal 8.33 for Jordan is 8 hours 20 minutes rounded for display; the cell still holds the exact value, so the total stays accurate.
Fixing common problems
The cell shows #####
Either the column is too narrow or the result is a negative time. Widen the column first by dragging the right edge of its column header. If the pound signs remain, the start is later than the end. Swap the cells in the formula, use the MOD version for overnight times, or add dates to both cells.
The answer looks like a clock time, a date or a long decimal
Excel picked a format for you. 8:45 AM or 1/0/1900 means the value is right but the format is a time of day or a date; apply h:mm or [h]:mm. A result like 0.364583 is the raw fraction of a day; apply a time format or multiply by 24 for hours.
You get #VALUE!
One of the times is text, often because it was pasted from another program or typed with a period or an extra space. Check it with =ISNUMBER(B2). Retype the time, or convert it with TIMEVALUE, which turns text such as "6:45 PM" into a real time value: =TIMEVALUE("6:10 PM")-TIMEVALUE("3:00 PM") returns 3:10 once formatted.
The minutes show as a month number
In a format code, m means minutes only when it sits right after an hour code or right before a seconds code. On its own, m is the month. Use h:mm or [mm]:ss, not a bare mm.
The total stops adding up past 24 hours
The sum is correct but the h:mm format is rolling it over. Change the total cell to [h]:mm.
A TEXT formula result will not add up
=TEXT(C2-B2,"h:mm") produces a tidy label, but the output is text, so SUM and further math will ignore it or fail. Keep the real subtraction in one column and build labels in another, always pointing later formulas at the real value.
A warning about the 1904 date system
Some instructions suggest turning on Use 1904 date system (on Windows: File > Options > Advanced, under When calculating this workbook) so that negative times display. Think hard before doing this in a workbook that already contains dates. The same date has a serial number 1,462 days apart in the two systems, so dates can end up showing four years and one day off, and dates pasted between workbooks that use different systems can shift too. Microsoft explains the two systems in change the date system. For most people the MOD or IF formulas above are the safer fix.
How to double-check the result, and undo a mistake
- Pick one row and work it out on paper. 8:30 AM to 5:15 PM is 8 hours 45 minutes, so the cell should show 8:45, and
=D2*24should return 8.75. - Temporarily set the result cell to General. You should see a fraction such as 0.364583. Multiply it by 24 in your head (about 8.75) to confirm, then use Undo (Ctrl+Z on Windows) to put the time format back.
- Click the result cell and read the formula in the formula bar. Confirm the end time comes first and that the cell references point to the right row after you filled the formula down.
- If something went wrong, press undo right away, or retype the formula. Because the start and end cells are never changed by the subtraction, you can rebuild the result column at any time without losing data.
Other things readers ask about time math
Can I show a negative time without changing the date system?
Yes, as a text label. =IF(C2>=B2,TEXT(C2-B2,"h:mm"),"-"&TEXT(B2-C2,"h:mm")) shows -0:15 when the time in C2 is 15 minutes earlier than the time in B2. The result is text, so keep a numeric version, such as =(C2-B2)*24, which can go negative without any trouble, for totals.
How do I subtract a number of minutes stored as a plain number?
If F2 holds 90 (meaning 90 minutes, not a time), divide it first: =A2-F2/1440. Subtracting 90 directly would remove 90 days.
Can I put the times straight into the formula?
Yes, with TIMEVALUE, as in =TIMEVALUE("6:10 PM")-TIMEVALUE("3:00 PM"). Cell references are easier to check and change, so use this only for one-off calculations.
Does this work the same way in Google Sheets?
The idea is the same, but the menus differ. The Sheets version is covered in how to subtract time in Google Sheets.
A good next step is to set up a small sheet with your own start and end times, use =MOD(C2-B2,1) for every row and give the total a [h]:mm format. That one pattern handles most day-to-day time subtraction. The reference for this pattern is Microsoft’s page on how to calculate the difference between two times.