Excel Formula for Time Difference: Examples and Fixes

To find the time between two values in Excel, subtract the start time from the end time: with the start time in A2 and 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; for a shift that crosses midnight without dates, use =MOD(B2-A2,1) instead.

How Excel stores time

Excel stores dates and times as numbers. A whole number represents a date, while the decimal part represents the time within that day: 0.5 is noon, and 0.25 is 6:00 a.m. Because both values are numeric, subtracting one from another calculates elapsed time. The displayed appearance depends on the cell’s number format, not on a different kind of time calculation.

For example, with a start time of 9:15 a.m. in A2 and an end time of 1:45 p.m. in B2, =B2-A2 returns 0.1875 as a stored value. Formatted as hours and minutes, it displays as 4:30. The arithmetic is 4 hours and 30 minutes; the decimal is a fraction of a 24-hour day.

Excel can usually recognize times typed with an AM/PM indicator or in a 24-hour format, such as 9:15 AM or 13:45. If a time is stored as text rather than as a numeric time, subtraction may return an error or an unexpected result. A quick clue is that changing the cell’s number format does not change how the value behaves. Use genuine Excel time values for reliable calculations.

Choose the right formula for your data

The simplest formula is right when both values belong to the same day. Other situations need a different approach, especially if the time range crosses midnight or the cells include dates. Choose based on what the source data actually records:

Same-day times: with a start time in A2 and end time in B2, use =B2-A2.
Overnight times without dates: with a start time in A2 and end time in B2, use =MOD(B2-A2,1).
Full date-and-time values: with the start timestamp in A2 and end timestamp in B2, use =B2-A2, then apply an elapsed-time format.

It is important not to use the overnight formula automatically for every record. MOD wraps a negative difference into a positive time within one day. That is useful for a shift from 10:30 p.m. to 6:15 a.m., but it can hide an incorrect end time or a duration longer than 24 hours if the data needs to represent multiple days.

Calculate an overnight time difference

When the cells contain only times of day, Excel cannot tell whether an end time earlier on the clock happened on the next day or was entered incorrectly. The MOD formula makes the common assumption that the end time is on the following day whenever it appears earlier than the start time.

For example, with a start time of 10:30 p.m. in A2 and an end time of 6:15 a.m. in B2, enter =MOD(B2-A2,1). The result is 7 hours and 45 minutes: 1 hour 30 minutes from 10:30 p.m. to midnight, plus 6 hours 15 minutes after midnight. Format the result as [h]:mm or h:mm to read it clearly.

MOD is appropriate for one overnight interval shorter than 24 hours. If shifts can last more than a day, or if you need to distinguish a same-day time from a next-day time, record the date as well as the time. Without dates, the formula cannot infer whether a time difference means 8 hours, 32 hours, or a data-entry mistake.

Use dates for multi-day durations

If each cell contains a complete date and time, direct subtraction preserves the calendar information. With a start timestamp in A2 of November 1, 2026 at 8:00 p.m. and an end timestamp in B2 of November 3, 2026 at 8:00 a.m., enter =B2-A2. The elapsed duration is 36 hours. A clock-style format that resets at midnight may show 12:00, which is why the format matters.

To display that example correctly, select the result cell and open Format Cells. On Windows, press Ctrl+1; on Mac, use Format > Cells. Choose Custom and enter [h]:mm as the format. The square brackets mean that hours continue accumulating instead of returning to zero after 24. The result then displays as 36:00. Use [h]:mm:ss when seconds matter.

Use full timestamps for attendance logs, equipment use, travel, or any interval that may span multiple dates. A date and time together also help expose mistakes: an end timestamp that falls before the start timestamp produces a negative duration rather than being silently treated as the following day. Check the dates and time zone or clock source if records come from systems that may use different local times.

Show elapsed hours, minutes, or seconds

A duration can be displayed as clock time or converted to a number for calculations and reporting. Excel’s underlying value is a portion of a day, so multiplying by the number of units in a day converts it to hours, minutes, or seconds.

With the start time in A2 and end time in B2 on the same day, use =(B2-A2)*24 for decimal hours. For 9:15 a.m. to 1:45 p.m., the result is 4.5 hours.
With the same cell layout, use =(B2-A2)*1440 for total minutes. The same example returns 270 minutes.
With the same cell layout, use =(B2-A2)*86400 for total seconds. The same example returns 16,200 seconds.

For decimal results, format the result cell as General or Number rather than as a time of day. If the result needs a fixed number of decimal places, use the number-format controls instead of rounding unless the calculation itself should be rounded. For example, 4 hours and 20 minutes is about 4.3333 decimal hours, not 4.20 hours: minutes are not hundredths of an hour.

For overnight data without dates, use the MOD calculation before converting units. With the start time in A2 and end time in B2, =MOD(B2-A2,1)*24 returns decimal hours. A 10:30 p.m. to 6:15 a.m. interval returns 7.75 hours. For full timestamps, multiply the direct subtraction by 24 instead; this retains durations above 24 hours.

Format results so they make sense

Excel may initially display a subtraction result as a decimal, a date, or a time that looks wrong. That does not necessarily mean the formula is wrong. Select the result cell and apply a format suited to the question: h:mm for a clock-like display under 24 hours, [h]:mm for cumulative hours, or [h]:mm:ss when seconds are needed. The brackets around h are the key difference for long durations.

On Windows, Ctrl+1 opens the Format Cells dialog. Choose Custom to enter an elapsed-time format. On Mac, open Format > Cells and choose the number-format options there. Excel for the web supports number formatting too, though the dialog layout may differ from the desktop application. If a workbook will be viewed in several versions of Excel, check the displayed result in the version used by the audience.

For a simple same-day time difference, formatting as h:mm is usually clear. However, a duration of 27 hours would display as 3:00 with that format because the hour display cycles through a day. Use [h]:mm for durations, especially totals, timesheets, and sums across several rows. You can also use decimal hours if a downstream calculation or report expects a numeric quantity.

Common errors and how to fix them

A result of ###### often means the column is too narrow, but it can also occur when a date-and-time calculation produces a negative value in Excel’s standard date system. Widen the column first. If the value remains unreadable, inspect the inputs and formula, and verify that the end really occurs after the start. A negative duration should not be converted to a positive one unless the data genuinely represents an overnight interval.

If a result appears as a small decimal such as 0.1875, the subtraction may be correct; apply an elapsed-time format. If it displays as a date or an unexpected clock time, change the result cell’s format rather than rewriting a valid formula. If the formula returns #VALUE!, check whether either input is text. Re-enter the values as times, or correct the source data so Excel recognizes them as numeric date/time values.

A frequent mistake is subtracting the end from the start. The usual order is end minus start: with start in A2 and end in B2, use =B2-A2. Another is assuming that 4:30 means 4.3 decimal hours. It means 4 hours and 30 minutes, or 4.5 decimal hours. For payroll, billing, or other rules-based reporting, confirm the organization’s rounding and break policies separately; a time formula calculates elapsed time but does not decide those rules.

Calculate durations down a list

For a table of shifts, put start times in column A, end times in column B, and durations in column C. With the first start time in A2 and first end time in B2, enter =MOD(B2-A2,1) in C2 if each shift is less than 24 hours and may cross midnight. Fill the formula down for the remaining rows, then format the duration column as [h]:mm. For shifts containing full dates and times, use =B2-A2 in C2 instead.

To total the durations, with the calculated shift durations in C2:C10, enter =SUM(C2:C10) in the total cell and format that cell as [h]:mm. The brackets are important here: a total of 30 hours should show as 30:00, not 6:00. If the total is meant to be decimal hours, with durations in C2:C10, use =SUM(C2:C10)*24 and format the answer as a number.

Keep the input columns in consistent formats and label whether the entries are times only or complete timestamps. This makes it easier to select the appropriate formula and spot records that cross dates. If the workbook uses formulas in a structured Excel table, entering a formula in a calculated column typically fills it through that column automatically; still verify the displayed format and the results at the bottom of the list.

Frequently Asked Questions

What is the basic Excel formula for time difference?
With the start time in A2 and end time in B2 on the same day, use =B2-A2. Format the result as h:mm for a short duration or [h]:mm if it may exceed 24 hours.

How do I subtract times when the shift passes midnight?
With a start time in A2 and end time in B2, use =MOD(B2-A2,1) when the interval is less than 24 hours and the earlier clock time means the next day. If you have dates as well, use full date-and-time values and subtract the start timestamp from the end timestamp.

How do I calculate the time difference in decimal hours?
With same-day start and end times in A2 and B2, use =(B2-A2)*24. For an overnight interval without dates, use =MOD(B2-A2,1)*24. Format the result as a number; 4 hours 30 minutes is 4.5 decimal hours.

Why does my Excel time formula show a decimal?
Excel stores time as part of a 24-hour day, so a result may appear as a fraction such as 0.1875. Apply a time or custom elapsed-time format to display it as hours and minutes, or multiply by 24 to show decimal hours.

How can I show a duration longer than 24 hours?
Format the result with [h]:mm or [h]:mm:ss. The brackets keep the hour count cumulative, so a duration of 36 hours displays as 36:00 rather than 12:00.

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.