OmniDocHow to Subtract Time in Excel Formulas
Learn the Excel formula for subtraction of time, handle shifts that cross midnight, and fix negative or ##### results with a few reliable steps.
Subtracting one time from another sounds like a one-line job until the result shows a decimal, a row of pound signs, or a negative number. The excel formula for subtraction of time is simple in principle, but Excel stores times as fractions of a day, and that changes how results look and what happens around midnight. This guide is for anyone tracking shift lengths, call durations, or work hours in a spreadsheet.
The short version: subtract the start time from the end time, then give the cell a time format. The sections below cover shifts that cross midnight, totals that run longer than a day, and negative results, which behave differently from date differences.
If your times are stored in a PDF timesheet, you can export the PDF to Excel with the PDF editor in the OmniDoc desktop client and apply these formulas to the exported sheet. Check the exported values before you rely on them.
What is the Excel formula for subtraction of time? The short answer
Subtract the start time from the end time, such as =B2-A2, and format the result as a time. For shifts that may cross midnight, wrap the difference with MOD so negative values turn positive.
| Your situation | What to do | Why |
|---|---|---|
| Both times fall on the same day | Use =B2-A2 | Excel subtracts the stored time values directly |
| The end time is after midnight | Use =MOD(B2-A2,1) | MOD adds one day to a negative difference |
| The total runs longer than a full day | Format the total as [h]:mm | A standard h:mm format resets after a full day |
| You need decimal hours | Use =MOD(B2-A2,1)*24 | One day equals 1, so one hour equals 1/24 |
| The result shows #### | Widen the column or check the date system | Negative times cannot display in the 1900 system |
Before you start
- Confirm the cells hold real times. Values entered as text will not calculate and can return #VALUE!. Numeric times usually align to the right in the cell, while text aligns to the left.
- Decide whether a shift can cross midnight. A plain subtraction of 22:00 minus 06:00 gives a negative number, so you need the MOD version in that case.
- Check the cell format. A cell set to General shows 0.35417 instead of 8:30. Formatting changes only the display, not the stored value.
- Know which date system your workbook uses. Excel workbooks use either the 1900 or 1904 date system. The 1900 system, the usual default on Windows, cannot display negative times, which affects the negative-result section below.
Subtracting times in Excel, step by step
- Enter the start time in A2 and the end time in B2. Type them with colons, such as 09:15 and 17:30, so Excel recognizes them as times rather than text.
- In C2, type =B2-A2. Excel subtracts the stored values and returns the difference as a fraction of a day.
- Apply a time format to C2. Use a format such as h:mm from the number format options so the result reads as 8:15 instead of a decimal.
- For a shift that crosses midnight, use =MOD(B2-A2,1) instead. If the start is 22:00 and the end is 06:00, the plain formula returns a negative value, while MOD returns 8:00.
- Copy the formula down the column. Drag the fill handle at the corner of the cell so each row uses its own start and end times.
- Total the durations with =SUM(C2:C20). Format the total with [h]:mm so hours beyond a full day keep counting.
- Convert to decimal hours if a payroll or billing system needs them. Use =MOD(B2-A2,1)*24, then format the cell as a plain number.
- Verify two or three rows by hand. Compare each result with a quick mental count before you copy the formula into the rest of the sheet.
Handling negative times and #### errors
A negative time difference does not display in the default 1900 date system. Excel shows #### in the cell even though the formula is correct. Subtracting a later time from an earlier one produces this result, so you may see it when a start time is mistyped.
If you need to show a negative duration, keep the calculation as a positive number and add the sign as text. For example, =IF(A2>B2,"-","")&TEXT(ABS(B2-A2),"h:mm") returns a leading minus sign when the end time is earlier than the start. The result is text, so you cannot add it to other durations; keep a numeric column for totals.
Switching a workbook to the 1904 date system can also display negative times, but it changes every date in the file, so test on a copy first.
Check the result
| Check | How to see it | If it's wrong |
|---|---|---|
| The result is a time | Look at the cell format; 8:15 is correct, 0.34375 is not | Apply a time format such as h:mm |
| No error or pound signs | Look for #VALUE! or #### in the cell | #VALUE!: retype the input as a time; ####: widen the column or use the text formula |
| Overnight shifts are positive | Compare with a shift you know, such as 22:00 to 06:00 | Replace the formula with MOD(B2-A2,1) |
| Totals are complete | Add two or three durations by hand | Format the total as [h]:mm |
When an error appears, start with the input cells. Most errors come from a time that Excel read as text or a formula that needs MOD for overnight shifts.
❓ FAQ
1. How do I subtract minutes from a time in Excel?
Use the TIME function with the minutes you want to remove, such as =A2-TIME(0,B2,0) to subtract the number of minutes stored in cell B2. Format the result as h:mm so it displays as a clock time.
2. Why does my time difference show a decimal like 0.35?
The cell is formatted as a number, so Excel displays the stored fraction of a day. Apply a time format such as h:mm to the cell, and the same value will read as 8:24.
3. Why does my total of hours reset after a full day?
A standard h:mm format wraps once the total passes a full day, so the display shows only the leftover hours. Format the total cell as [h]:mm, with the brackets, and it will display every hour in the total.
4. Can I subtract times that include dates?
Yes. When both values include a date and a time, the result is in days, so multiply by 24 to get hours. Our separate guide covers date-only differences in more detail.
📝 Summary
The excel formula for subtraction of time is usually a plain end-minus-start formula, with MOD for shifts that cross midnight and [h]:mm for totals longer than a day. Check the formats, test a few rows by hand, and the rest of the column should follow.
