To subtract one time from another in Excel, enter the start time in A2 and the end time in B2, then use =B2-A2. Format the result as a duration, such as [h]:mm; if the time period crosses midnight, use =MOD(B2-A2,1) instead.
How Excel stores and subtracts times
Excel stores times as fractions of a 24-hour day. Midnight is zero, 6:00 AM is 0.25, noon is 0.5, and 6:00 PM is 0.75. A time subtraction therefore returns a fraction of a day: subtracting 9:15 AM from 1:00 PM produces 0.15625, which represents 3 hours and 45 minutes.
Excel usually displays that underlying result as a clock time, not as a decimal fraction. That is why the result cell’s number format matters. The formula calculates the difference; the format determines whether you see a clock-style value, a duration, or a number of hours.
Enter genuine Excel times, rather than text that merely looks like a time. In a cell, type values such as 9:15 AM or 13:00. Excel normally recognizes these as times, although accepted date and time entry can vary with regional settings. If a value is left-aligned or calculations behave unexpectedly, check whether it is stored as text.
Subtract two times on the same day
For a shift or event that starts and ends on the same day, subtract the start from the end. With the start time in A2 and the later end time in B2, enter =B2-A2. For example, if A2 contains 9:15 AM and B2 contains 1:00 PM, the difference is 3 hours and 45 minutes.
To show that result clearly, select the result cell and open Format Cells. On Windows, press Ctrl+1; on Mac, press Command+1. Choose the Custom category and enter h:mm or [h]:mm in the Type box. The square-bracket version is usually the safer choice for elapsed durations because it can show totals greater than 24 hours.
For a simple calculation, a typical layout is:
- A2: start time, such as 9:15 AM
- B2: end time, such as 1:00 PM
- C2: elapsed time, using
=B2-A2
Copy the formula down column C for more rows. Each row will calculate its own end-minus-start difference, provided the times are on the same day and the end is later than the start.
Calculate a time difference across midnight
Ordinary subtraction gives a negative result when the end time is earlier on the clock than the start time. That commonly happens with an overnight shift: 10:00 PM followed by 6:30 AM looks like a backwards subtraction unless the next-day boundary is accounted for.
With the start time in A2 and the next-morning end time in B2, use =MOD(B2-A2,1). If A2 is 10:00 PM and B2 is 6:30 AM, the formula returns 8 hours and 30 minutes. Format the result as [h]:mm to display it as 8:30.
MOD wraps the negative difference back into a 24-hour day. That is useful when the elapsed period is known to be shorter than 24 hours and crosses midnight once. It does not establish how many calendar days passed. For example, time-only entries of 7:00 AM and 9:30 AM cannot tell Excel whether the second time is two and a half hours later or 26 and a half hours later. Use dates as well as times when the duration could span a full day or more.
Show the result as decimal hours or minutes
Some reports need a numeric quantity for calculations, such as 3.75 hours rather than 3:45. A time difference is stored as a fraction of a day, so multiply it by 24 to convert it to hours. With the start time in A2 and the same-day end time in B2, enter =(B2-A2)*24. For 9:15 AM to 1:00 PM, the result is 3.75. Format the result cell as General or Number, not as a time.
For an overnight period of less than 24 hours, use the wrapped difference before converting it. With the start in A2 and end in B2, enter =MOD(B2-A2,1)*24. From 10:00 PM to 6:30 AM, this returns 8.5 decimal hours. The same example expressed as elapsed minutes is 510, because eight and a half hours contains 510 minutes.
With the start in A2 and end in B2, enter =MOD(B2-A2,1)*1440 to calculate minutes for an interval that crosses midnight once or stays within one day. There are 1,440 minutes in a day, so multiplying the day fraction by 1,440 converts it to minutes. If the calculation includes actual dates or can exceed a day, subtract the full date-time values and multiply that difference by 1,440 instead of using MOD.
Use decimal hours when downstream calculations require a number, such as multiplying hours by an hourly quantity. Use an elapsed-time display such as [h]:mm when readers need to interpret the duration as hours and minutes. Avoid mixing the two: a decimal value of 3.75 means 3 hours and 45 minutes, not 3 hours and 75 minutes.
Use dates when a duration can exceed 24 hours
When an event may last into another day, enter both the date and time for each endpoint. For example, put the start date and time, March 3, 2026 at 10:00 PM, in A2 and the end date and time, March 4, 2026 at 6:30 AM, in B2. With those complete date-time values in A2 and B2, use =B2-A2. The result is 8 hours and 30 minutes.
Format this result as [h]:mm. Square brackets around h tell Excel to display cumulative hours rather than restart the hour count at each 24-hour boundary. This distinction matters for a 30-hour event: a clock-style format can show the time of day within the cycle, while [h]:mm displays the elapsed total as 30:00.
Date-time values also prevent ambiguity in longer intervals. A time-only formula using MOD assumes the relevant interval wraps once and is under 24 hours. Complete dates let ordinary subtraction distinguish, for example, a 26-hour period from a two-hour period. Keep the dates attached to their times; subtracting only the time portions discards the day information.
Choose the right formula and number format
The right approach depends on what the entries represent and how the answer will be used:
- Same-day start and end, with an elapsed-time display: subtract end minus start and format as
[h]:mm. - Overnight start and end times, with a duration under 24 hours: use
=MOD(B2-A2,1)and format as[h]:mm. - Full date-and-time values, including durations over 24 hours: subtract the start date-time from the end date-time and use
[h]:mm. - Decimal hours for a same-day time-only interval: multiply the difference by 24 and display the result as a number.
- Minutes for a time-only interval under 24 hours: multiply the wrapped difference by 1,440 and display the result as a number.
Use a duration format rather than a regular clock format when the answer means “time elapsed.” The display h:mm is easy to misread for totals beyond a day because it can cycle through the 24-hour clock. The format [h]:mm is designed to show accumulated hours. A number format changes only the appearance, not the underlying value used in arithmetic.
Fix common subtraction problems
- The result shows ####. The column may be too narrow, so widen it first. If the result is negative, Excel’s standard 1900 date system cannot display it as a normal time, and the cell may also show hash marks. Check that the start and end are in the intended order or use the overnight formula where appropriate.
- The result looks like a time of day. Change the result cell to
[h]:mmfor elapsed hours and minutes, or to General or Number for decimal hours. A formula can be correct even when the current format makes its result confusing. - The result is zero or unexpected. Check that the inputs are real time values rather than text, and confirm that the formula refers to the intended cells. Also verify whether the entries include dates; time-only values cannot identify a duration over 24 hours.
- A shift crosses midnight. Use MOD for a time-only interval under 24 hours, or enter full start and end dates and subtract the complete date-time values. Do not use MOD when you need to preserve multi-day duration.
- The answer is off by a factor of 24 or 1,440. A raw Excel time difference is measured in days. Multiplying by 24 gives hours; multiplying by 1,440 gives minutes. If you multiply but then format the number as a time, the display may become misleading.
For a schedule with breaks, subtract the start from the end first, then subtract the break duration if it is entered as a valid Excel time. Check that the break is recorded in the same units and that it does not exceed the elapsed shift. If entries can be incomplete, avoid treating blank cells as meaningful times: confirm the required inputs are present before relying on a calculated total.
Frequently Asked Questions
What is the formula to subtract time in Excel?
For a same-day interval, put the start time in A2 and the end time in B2, then enter =B2-A2. Format the answer as [h]:mm to show elapsed hours and minutes rather than a fraction of a day.
How do I subtract times in Excel when the end time is after midnight?
For time-only entries where the interval is under 24 hours, put the start in A2 and end in B2 and use =MOD(B2-A2,1). For example, 10:00 PM to 6:30 AM returns 8:30 when the result is formatted as [h]:mm.
How do I get decimal hours between two times?
For a same-day interval with the start in A2 and end in B2, use =(B2-A2)*24 and format the answer as a number. A result of 3.75 represents three hours and 45 minutes.
Why does Excel show a negative time or ####?
The end time may be earlier than the start time, or the formula may be subtracting the cells in the wrong order. If the interval crosses midnight, use MOD for a time-only period under 24 hours or enter complete dates and times. A narrow column can also display hash marks, so widen it to rule that out.
How do I calculate a time difference longer than 24 hours?
Include the date and time in both the start and end cells, subtract the earlier date-time from the later one, and format the result as [h]:mm. The dates tell Excel how many days elapsed, while the bracketed hour format displays the total hours instead of resetting at 24.
Related
- Power Query Editor: How to Split Text into Multiple Columns
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Create a Table in Excel With the Keyboard ShortcutTo create a table quickly in Excel for Microsoft 365 on Windows, click a cell inside your data…
- Excel Text Usage Examples for Cleaning and FormattingExcel text examples often use TEXT to turn a number or date into a chosen display, such as…
- Oracle Text to Columns: Split Strings into FieldsOracle does not have a single database command called Text to Columns. To split a delimited string into…
- Name Badge Template in Word: Create One Step by StepCreate a reusable name badge template in Word by setting up a page and sizing a table to…
- Excel Certifications: The Current Options ExplainedThe main Microsoft Excel certifications are Microsoft Office Specialist (MOS) Excel Associate for Microsoft 365 Apps (exam MO-210)…
- Can You Do Text to Columns Vertically in Excel?Not directly: Excel’s Text to Columns command splits text across separate columns, not down separate rows. To split…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.