Excel Time Between Two Times: Formulas and Overnight Shifts

Formulas & functions · Updated November 18, 2026

To calculate the time between two times in Excel, subtract the start time from the end time: if the start is in A2 and the end is in B2, enter =B2-A2. Format the result as a time; if the interval may cross midnight, use =MOD(B2-A2,1) instead.

How Excel stores times

Excel stores dates and times as numbers. A time is the fractional part of a day: midnight is 0, noon is 0.5, and 6:00 a.m. is 0.25. This is why subtracting one time from another produces a fraction that Excel can display as an elapsed time. For example, an eight-hour interval is one-third of a day, or approximately 0.3333 as a stored value.

When a cell contains a date as well as a time, Excel uses the whole-number part for the date and the fractional part for the time. That makes subtraction useful for durations spanning dates, too: subtracting an earlier date-and-time value from a later one returns the elapsed number of days, including any partial day.

The displayed result depends on the cell’s number format. The underlying result may be correct even when the cell shows a decimal, a clock time, or an unexpected value. Choose a format that communicates the duration you intend to show.

Calculate a same-day duration

For a shift, appointment, or task that begins and ends on the same day, enter the start time in A2 and the end time in B2, then put =B2-A2 in C2. If A2 contains 9:15 AM and B2 contains 5:45 PM, the result is 8:30, meaning eight hours and 30 minutes.

To enter a time, type a value such as 9:15 AM or 17:45 into a cell and press Enter. Excel usually recognizes either a 12-hour time with an AM/PM marker or a 24-hour time. If a value is stored as text rather than recognized as a time, subtraction may return an error or behave unexpectedly. Changing the cell’s appearance to a time format does not necessarily convert text into a real time value.

To calculate durations for multiple rows, put each start and end time on its own row, enter the subtraction formula for the first row, and fill the formula down. In Excel for Microsoft 365 on Windows, drag the fill handle at the lower-right corner of the formula cell, or copy and paste the formula into the rows below. Relative references adjust automatically, so the next row uses its own start and end cells.

Handle times that cross midnight

Ordinary subtraction can produce a negative result when an interval crosses midnight. For example, a shift from 10:40 PM to 6:10 AM ends on the next calendar day, but if the cells contain only clock times, Excel has no date information to indicate that. The straightforward overnight formula is =MOD(B2-A2,1), assuming A2 contains the start time and B2 the end time. For 10:40 PM and 6:10 AM, it returns 7:30.

MOD wraps a negative time difference around one full day. It is convenient when every interval is less than 24 hours and the end time is earlier than the start time only because midnight intervened. If the start and end are the same, the formula returns zero, not 24 hours; clock times alone cannot tell Excel whether the interval was zero or a full day.

An alternative makes the overnight rule more visible: with the start time in A2 and end time in B2, use =IF(B2<A2,B2+1-A2,B2-A2). This adds one day when the end clock time is earlier than the start. It also returns 0 when the two times are equal. Use this version if you want the formula’s logic to be explicit, or MOD if a compact formula is preferable.

For a shift that may last 24 hours or longer, enter both the date and time for each endpoint instead of relying on a wraparound formula. With the start date and time in A2 and the later end date and time in B2, use =B2-A2. Because the dates are included, Excel can calculate a duration of one day, several days, or more accurately. MOD would discard whole days, so it is not suitable for that situation.

Show elapsed hours correctly

A duration is not always best displayed as a clock reading. For a total that could exceed 24 hours, use an elapsed-hours format so Excel does not cycle back to zero after a day. Select the result cells, open Format Cells with Ctrl+1 on Windows, choose the Number tab and Custom category, and enter [h]:mm in the Type box. On Mac, use Command+1 to open Format Cells; the dialog’s number-format controls may be arranged differently.

The square brackets around h are important. A format of h:mm displays the hour within a 24-hour clock cycle, so a 27-hour duration can appear as 3:00. The format [h]:mm displays the total hours, so the same duration appears as 27:00. You can use [h]:mm:ss when seconds matter. A value such as 8:30 in this format means eight hours and 30 minutes, not 8:30 a.m.

For an ordinary same-day interval shorter than 24 hours, a standard time format such as h:mm may appear to work. Using [h]:mm consistently is safer for workbooks where totals may grow beyond a day, including summed task times or several shifts.

Convert the duration to decimal hours

Times are fractions of a day, so multiply a duration by 24 to express it as hours in decimal form. With the start time in A2 and end time in B2, use =(B2-A2)*24. A result of 8.5 represents eight and a half hours, which is often easier to use in calculations than a displayed duration of 8:30.

For an overnight interval under 24 hours, multiply the wrapped difference by 24: with start in A2 and end in B2, use =MOD(B2-A2,1)*24. A shift from 10:40 PM to 6:10 AM returns 7.5 decimal hours. The arithmetic is 7 hours plus 30 minutes, and 30 minutes is 0.5 hour.

To show a fixed number of decimal places, format the result cell as Number and choose the desired decimal places, or round in the formula. With start in A2 and end in B2, =ROUND(MOD(B2-A2,1)*24,2) returns 7.5 for that overnight example, displayed numerically as 7.5 or 7.50 depending on the cell format. Rounding changes the stored result, so use it only when that level of precision is appropriate; a number format can change what is shown without changing the calculation.

Sum durations and calculate breaks

When you add several duration cells, format the total with [h]:mm if it could reach 24 hours. For example, if daily durations are in C2:C8, enter =SUM(C2:C8) in C9 and apply the [h]:mm format. Without the brackets, a total of 26 hours may display as 2:00, even though the sum itself is correct.

If you track a break, calculate it as part of the elapsed time. With the start in A2, end in B2, and break duration in C2, enter =MOD(B2-A2,1)-C2 for an interval shorter than 24 hours. If the shift runs from 9:00 AM to 5:30 PM and the break is 30 minutes, the elapsed time is 8:30 and the result after subtracting the break is 8:00. Enter C2 as a duration, such as 0:30, rather than as a decimal number of minutes. If a result is negative, check that the break is shorter than the elapsed time and that all entries are real time values.

For decimal net hours, use the same break layout and enter =(MOD(B2-A2,1)-C2)*24. With those example values, the result is 8. If your records include date-and-time endpoints over multiple days, replace the wrapped difference with the direct difference between the later and earlier date-time cells.

Common errors and how to fix them

  • Negative or hash-mark display: A same-day subtraction may be negative if the interval crosses midnight. Use the overnight formula or enter dates with the times. A negative time may display as a row of hash marks under the workbook’s date system.
  • A total unexpectedly resets after 24 hours: Change the result format from h:mm to [h]:mm. This is a display issue when the duration calculation and sum are otherwise correct.
  • The formula gives an error or an implausible answer: Check that the inputs are actual Excel times, not text. Retype the values in a recognized form, or inspect whether imported data includes spaces or other characters.
  • AM and PM are reversed: Confirm that each value represents the intended point in the day. For instance, 7:00 without an AM/PM marker may not mean the same thing in every context if it was entered as text or interpreted differently.
  • The result shows a decimal: Apply an elapsed-time format such as [h]:mm to display hours and minutes, or multiply by 24 if decimal hours are what you need.
  • Equal times return zero: This is expected when Excel has only two clock times. Enter dates as well if equal clock readings might mean a full-day interval.

Frequently Asked Questions

What is the formula for time difference in Excel?
For a start time in A2 and an end time in B2 on the same day, use =B2-A2. Apply a time format to the result. If the interval crosses midnight and is under 24 hours, use =MOD(B2-A2,1).

How do I calculate hours between two times in Excel?
Subtract the start from the end and multiply by 24. With start in A2 and end in B2 for a same-day interval, use =(B2-A2)*24. For an overnight interval under 24 hours, use =MOD(B2-A2,1)*24.

How do I calculate time between two times when the shift ends the next day?
If the cells contain only times, use MOD to wrap the difference across midnight, such as =MOD(B2-A2,1) with the start in A2 and end in B2. If the interval might be 24 hours or longer, include the date with each time and subtract the earlier date-time from the later one.

Why does my Excel total show the wrong hours after 24 hours?
Excel may be displaying a correct total using a clock format that rolls over every 24 hours. Select the total and apply the custom number format [h]:mm. The brackets tell Excel to show cumulative hours rather than the hour on a 24-hour clock.

Can Excel calculate minutes between two times?
Yes. With start in A2 and end in B2, use =(B2-A2)*1440 for a same-day interval, because a day contains 1,440 minutes. For an overnight interval under 24 hours, use =MOD(B2-A2,1)*1440. These formulas return a number of minutes.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.