Excel Time Between: Calculate Hours and Minutes

How-to basics · Updated November 18, 2026

To calculate time between two times 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 [h]:mm to show elapsed hours and minutes, including totals longer than 24 hours. If the times cross midnight and have no dates, use =MOD(B2-A2,1) instead.

How Excel Stores Time

Excel stores dates and times as numbers. A whole number represents a day, and the fractional part represents the time within that day: 0.5 is noon, for example, and 0.25 is 6:00 a.m. Subtracting one valid date-time value from another therefore gives a duration as a fraction of a day. The subtraction is simple; choosing a display format that makes the result readable is the part that often causes confusion.

For example, with 9:15 AM in A2 and 5:45 PM in B2, =B2-A2 returns a value equivalent to 8 hours and 30 minutes. If the result cell is formatted as a time of day, Excel may show a clock time rather than a duration. A duration is not itself a time of day: 8 hours and 30 minutes describes an amount of elapsed time, not a point on the clock.

Calculate the Difference Between Two Times

For start and end times on the same day, subtract the earlier time from the later one. With a start time in A2 and an end time in B2, enter =B2-A2. For a shift from 8:30 AM to 4:00 PM, the result is 7 hours and 30 minutes. Excel does not require the times to use the same display style, but both cells need to contain actual Excel time values rather than text that merely looks like a time.

To enter a time, type a recognizable value such as 8:30 AM or 16:00 and press Enter. Excel can display the same value in different formats without changing its underlying value. If a subtraction produces an unexpected result, check whether either input is stored as text; changing a cell’s appearance to a time format does not always convert text into a number.

Handle Times That Cross Midnight

If a shift starts before midnight and ends after midnight, the end clock time is numerically smaller than the start time. Direct subtraction then produces a negative value, which Excel’s default date system generally cannot display as an ordinary time. For a single overnight interval where only clock times are entered, use the MOD function.

With the start time in A2 and the end time in B2, enter =MOD(B2-A2,1). For a shift from 10:00 PM to 6:00 AM, the result is 8 hours. MOD wraps the negative difference into the range of one day, which is useful for a single interval shorter than 24 hours. It also returns zero when the start and end times are identical, so it cannot tell whether that means no time elapsed or a full 24-hour interval.

When an interval might span a full day or more, enter the actual dates along with the times. For example, use a start date-time in A2 and the end date-time in B2, with the end date set to the following day. Then use =B2-A2. Including dates removes the ambiguity: the values indicate which day each time belongs to, and the subtraction can represent durations of 24 hours or longer.

Format the Result as Elapsed Time

The format controls how Excel displays a calculated value, not the underlying duration. The format h:mm can show hours and minutes for a short interval, but its hour component cycles after 24 hours. For a workweek total or any duration that may reach a day, use the elapsed-time format [h]:mm. The square brackets tell Excel to accumulate hours instead of displaying only the hour within a 24-hour clock cycle.

In Excel for Microsoft 365 on Windows, select the result cells and press Ctrl+1. In Format Cells, choose Number, then Custom, and enter [h]:mm in the Type box. On a Mac, select the cells and press Command+1 to open Format Cells; the custom format is also available there. In Excel for the web, select the cells and use Home > Number Format > More Number Formats to reach the number-format options. The exact dialog layout can differ slightly between desktop and web versions.

Use [h]:mm:ss when seconds matter. If you need a total expressed as minutes, the custom format [m] displays cumulative minutes. These formats do not round the calculated value; they change how much of it is shown. For example, a duration of 26 hours and 15 minutes displays as 26:15 with [h]:mm, rather than wrapping around to 2:15.

Show Decimal Hours or Minutes

Time formats are convenient for schedules, but decimal hours may be easier to use in a report or calculation. Because Excel stores one day as 1, multiply a time difference by 24 to convert it to hours. With a start time in A2 and an end time in B2 on the same day, enter =(B2-A2)*24. For 8:30 AM to 5:00 PM, the result is 8.5 hours. Format the result as a number if Excel displays it as a clock time.

To convert the same difference to minutes, multiply by 1,440, the number of minutes in a day. With the start time in A2 and the end time in B2 on the same day, enter =(B2-A2)*1440. A period of 1 hour and 45 minutes returns 105. If the interval crosses midnight, use MOD around the difference before multiplying: with start and end clock times in A2 and B2, enter =MOD(B2-A2,1)*24 for decimal hours or =MOD(B2-A2,1)*1440 for minutes.

Decimal hours are not the same as hours and minutes written with a colon. For instance, 7.5 hours means 7 hours and 30 minutes; it does not mean 7 hours and 5 minutes. Keep the unit clear in column headings and reports so readers do not confuse decimal values with clock-style durations.

Subtract an Unpaid Break

For a work shift with one unpaid break, subtract the break duration as well as the start time from the end time. If A2 contains the shift start, B2 the shift end, C2 the break start, and D2 the break end, enter =(B2-A2)-(D2-C2) for a shift and break on the same day. For a shift from 9:00 AM to 5:30 PM and a break from 12:30 PM to 1:00 PM, the result is 8 hours. Format the result as [h]:mm if you want it displayed as elapsed time.

This calculation assumes that the break start and end are entered as times on the same date and that the break falls within the shift. For shifts crossing midnight, full date-time values are the clearest option: enter dates as well as times for the shift and break, then subtract the dated break duration from the dated shift duration. If a schedule has several unpaid breaks, calculate each break duration and subtract their total. Check the inputs for overlaps or breaks outside the shift, since a formula cannot determine whether the schedule itself is sensible.

Common Problems and Checks

  • The result shows ####. The column may be too narrow, or the result may be a negative time under the workbook’s date system. Widen the column first. For an overnight interval made from clock times alone, use MOD; for intervals that can span whole days, enter dates with the times.
  • The result looks like a time of day. Apply [h]:mm to display elapsed hours. A regular clock format can wrap after 24 hours.
  • The answer is wrong despite the formula. Confirm that the start and end cells contain numeric Excel date-time values and that the correct dates are included. Text values, an incorrect AM/PM entry, or a missing next-day date can change the result.
  • A zero result seems suspicious. With clock times alone, identical start and end values could mean zero hours or a full day. Use dated values when the intended duration is 24 hours or more.

One useful check is to temporarily format the input cells as General or Number. Valid Excel times appear as decimal values, while text entries do not behave like ordinary numbers. Another check is to test the calculation with a short known interval before filling the formula down a long list. When copying a formula to other rows, keep the start and end references on the same row so each result uses its own pair of times.

Frequently Asked Questions

How do I calculate hours between two times in Excel?
For times on the same day, subtract the start from the end. With start in A2 and end in B2, use =B2-A2, then format the result as [h]:mm. Multiply the difference by 24 if you need decimal hours.

How do I calculate time between two times when the shift crosses midnight?
If the cells contain clock times only and the interval is less than 24 hours, use =MOD(B2-A2,1) with start in A2 and end in B2. If the interval may last 24 hours or more, include dates and subtract the full date-time values instead.

Why does Excel show #### for a time difference?
First widen the column. If it still shows ####, the result may be a negative time, often because an overnight end time is earlier on the clock than its start. Use MOD for a single overnight interval with clock times, or add the correct dates to both values.

How do I show more than 24 hours?
Format the result as [h]:mm, not h:mm. The brackets make Excel show cumulative hours, so a duration of 30 hours and 20 minutes appears as 30:20 rather than 6:20.

Can Excel calculate the difference between two dates and times?
Yes. Enter a complete date and time in each cell, then subtract the earlier value from the later one. The result is elapsed time in days as a number, so format it as [h]:mm or multiply it by 24 to show decimal hours.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.