To subtract one time from another in Excel, subtract the start time from the end time: with a start time in A2 and an end time in B2, enter =B2-A2. Format the result as a duration, such as [h]:mm, to show elapsed hours and minutes clearly. If the time period crosses midnight, use a different formula or include the dates, as explained below.
How Excel stores time
Excel stores dates and times as numbers. A whole-number portion represents days, while the decimal portion represents the time within a day. For example, 6:00 AM is one quarter of a day, or 0.25, and noon is 0.5. When you subtract two time values, Excel subtracts those underlying numbers; the result is the elapsed portion of a day.
This is why a result can look unexpected even when the calculation is correct. A duration of 90 minutes is stored as 0.0625, but a cell using a general number format may display that decimal rather than 1:30. The value has not changed: the cell’s number format determines how Excel presents it.
For reliable calculations, enter times as actual Excel time values, not text that merely looks like a time. Typing 9:00 AM into a cell normally creates a time value. A value imported from another system may instead be text, especially if it contains extra spaces or uses a format Excel does not recognize. Text values may not subtract as expected.
Subtract times on the same day
For a start and end time on the same day, put the start in A2 and the end in B2. For example, if A2 is 9:15 AM and B2 is 12:30 PM, enter =B2-A2 in C2. The result is 3 hours and 15 minutes, usually displayed as 3:15 when the result cell has a suitable time format.
The subtraction order matters: use end minus start. Reversing the references gives a negative result. This basic method works for meetings, task durations, machine run times, and other intervals that begin and end on the same calendar day. Copy the formula down a column to calculate each row independently.
Make sure the start and end values really refer to the same day. If a shift begins in the evening and ends after midnight, a plain subtraction may produce a negative value or an error display. Use the overnight method in the next section rather than changing the subtraction order.
Format the answer as elapsed time
To apply a duration format in Excel for Microsoft 365 on Windows, select the result cells and press Ctrl+1. In the Format Cells dialog, choose the Number tab, select Custom, enter [h]:mm in the Type box, and select OK. The square brackets tell Excel to show total elapsed hours instead of wrapping the display at 24 hours. On Excel for Mac, Command+1 opens Format Cells; the custom format code is the same.
A format of h:mm can be suitable for a single interval shorter than a day, but it may show only the hour within a 24-hour clock cycle when the result is longer than 24 hours. For example, a total of 27 hours can appear as 3:00 with a format that wraps at one day. The [h]:mm format displays the total as 27:00, which is generally more useful for accumulated durations.
Formatting changes the display, not the underlying value. You can still use a duration-formatted result in further calculations, such as adding it to another duration or multiplying it by a rate. If a cell displays ####, widen the column first. If the result is negative, check the time order and whether the interval crosses midnight.
Handle an interval that crosses midnight
If you have only times and the interval is known to be less than 24 hours, use MOD to wrap a negative difference into the next day. With the start time in A2 and the end time in B2, enter =MOD(B2-A2,1). If A2 is 10:30 PM and B2 is 1:00 AM, the result is 2:30. Format the result as [h]:mm.
MOD is useful for recurring daily schedules, but it assumes the end time is on the same day or the immediately following day and that the intended duration is under 24 hours. It cannot tell whether an end time of 1:00 AM means the next morning or a different date. If an interval could last more than 24 hours, or the dates matter, enter full date-and-time values instead.
For example, if A2 contains 10:00 PM on November 2 and B2 contains 2:00 AM on November 3, the date makes the order unambiguous. The formula is the same as for a same-day subtraction: with the starting date and time in A2 and the ending date and time in B2, enter =B2-A2. The elapsed result is 4:00. Use [h]:mm to display it as a duration.
Calculate decimal hours or minutes
Sometimes a result needs to be a number rather than a time display. Decimal hours are useful for summaries or calculations that expect hours as a quantity. With a same-day start time in A2 and end time in B2, enter =(B2-A2)*24. For a start of 9:15 AM and an end of 12:30 PM, the result is 3.25 hours: three hours plus 15 minutes, and 15 divided by 60 is 0.25. Format the result as a number if you want to show decimal places.
To return elapsed minutes instead, multiply the difference by 1,440, the number of minutes in a day. With the same start in A2 and end in B2, enter =(B2-A2)*1440. The 3-hour-15-minute example returns 195 minutes. These multipliers convert Excel’s fraction-of-a-day result into the unit you need.
For an overnight interval, use the wrapped duration before converting to a number. With the start in A2 and end in B2, enter =MOD(B2-A2,1)*24 to return decimal hours. A 10:30 PM to 1:00 AM interval returns 2.5. If the dates are present in the cells, use the direct date-and-time difference instead; multiplying that difference by 24 also gives elapsed hours without wrapping.
Subtract breaks and add durations
To subtract an unpaid break, enter the start time in A2, end time in B2, and break duration in C2. If the start is 9:00 AM, the end is 5:00 PM, and the break is 0:30, enter =B2-A2-C2 in D2. The result is 7:30, or seven hours and 30 minutes. Enter the break as a duration, not as a clock time such as 12:30 PM.
For several work periods, calculate each period’s duration in its own row, then add the duration cells. With five daily durations in D2:D6 of 8:00, 7:30, 8:15, 7:45, and 8:30, enter =SUM(D2:D6) in D7. The total is 40:00. Format D7 as [h]:mm; otherwise, Excel may display only the time within a 24-hour cycle.
If the periods cross midnight, calculate each row with a method that accounts for midnight, or use full date-and-time values, before summing. Also check whether a break is recorded in the same units and format as the other values. A break entered as 0.5 means half a day in Excel, not half an hour; enter 0:30 as a time duration or convert the break consistently.
Common time subtraction problems
- The result is ####. Widen the column. If it still does not display, the calculated value may be negative and the workbook’s date system may not display negative times as ordinary time values. Check the start and end order, and use full date-and-time entries when appropriate.
- The answer shows a decimal. The calculation may be correct but the result cell is formatted as General or Number. Apply a duration format such as
[h]:mmfor hours and minutes. - A total above 24 hours appears to reset. Change the result format from a wrapping hour format to
[h]:mm. The bracketed hour displays accumulated hours. - The answer is zero or the formula does not calculate. Check whether the inputs are stored as text. A value aligned or behaving differently from nearby time entries can be a clue, though alignment alone is not conclusive. Re-enter the values as times or use Excel’s data-conversion tools if they came from an import.
- The overnight result is much too long or too short. Decide whether the cells contain clock times only or full dates and times. MOD is appropriate for a known, under-24-hour interval from time-only values; direct subtraction is appropriate when actual dates are included.
A common misconception is that changing the number format fixes an incorrect calculation. Formatting can make a valid duration readable, but it cannot determine which day a time belongs to, turn text into a time value in every case, or correct reversed references. Confirm the inputs and the formula first, then adjust the display.
Choose the right subtraction method
Use plain end-minus-start subtraction when both times are on the same day. Use MOD when you have time-only entries, know the interval crosses midnight if necessary, and know it is shorter than 24 hours. Use full date-and-time entries with direct subtraction when dates are available, durations may exceed a day, or you need the result to reflect the actual calendar interval.
Choose the output format based on what you will do with the answer. Use [h]:mm to read a duration, decimal hours for calculations that require a numeric hour quantity, or total minutes when that is the required unit. A clear layout with separate start, end, break, and duration columns makes formulas easier to audit and reduces ambiguity about overnight work.
Frequently Asked Questions
How do I subtract time in Excel and get hours and minutes? Put the start time in A2 and the end time in B2, enter =B2-A2 in another cell, and format the result as [h]:mm. For an interval crossing midnight, use the overnight approach above or include the actual dates.
How do I calculate hours between two times in Excel? Subtract the start from the end and display the result as [h]:mm for hours and minutes. To return decimal hours instead, multiply the time difference by 24. Use dates as well as times if the interval crosses midnight or may be longer than a day.
Why does Excel show a negative time or ####? A negative result often means the end time is earlier than the start time, as happens with time-only entries for an overnight shift. Use MOD for a known short overnight interval, or enter dates and times. A narrow column can also cause ####, so widen it before diagnosing the formula.
How do I add time totals over 24 hours? Add the duration values with SUM, then format the total cell as [h]:mm. The brackets prevent Excel from displaying only the hour remainder after each 24-hour period.
Can I subtract time when the dates are different? Yes. Enter each value as a full date and time, then subtract the starting date-and-time value from the ending value. Format the result as [h]:mm for elapsed hours and minutes, or multiply the difference by 24 if you need decimal hours.
Related
- Data Validation Reference Table in Excel
- Remove Duplicates in Power Query: A Practical Guide
- Power Query Basics: Import, Clean, and Transform Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MIN Ignore Errors: Formulas and Common FixesTo find the smallest number in a range while ignoring error cells, use =AGGREGATE(5,6,A2:A10). The 5 selects MIN,…
- Copy Formulas in Excel Across Rows: A Simple GuideTo copy a formula horizontally, select its cell and drag the small fill handle at the lower-right corner…
- Sort Excel Column Without a Header Safely in ExcelTo sort an Excel column that has no header, select the data cells, then choose Data > Sort…
- SUMIF Between Two Values in Excel: Formula & ExamplesTo sum values between two limits in Excel, use SUMIFS, which applies both a lower and an upper…
- How to Count Unique Values in an Excel PivotTableTo count distinct values in an Excel PivotTable, create the PivotTable with Add this data to the Data…
- Excel Formula to Combine Text From Two Cells with a SpaceWith text in A1 and B1, join the contents directly with =A1&B1; to put a space between them,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.