Excel Time Difference Between Two Dates and Times Explained

To find the elapsed time between two date-and-time values in Excel, subtract the earlier value from the later one: with the start in A2 and the end in B2, enter =B2-A2. Format the result as [h]:mm to show total elapsed hours and minutes, including durations longer than 24 hours.

How Excel stores dates and times

Excel stores a date as a serial number: whole-number increments represent days. A time is stored as a fraction of a day, so noon is 0.5 and 6 a.m. is 0.25. When a cell contains both a date and a time, its value combines the date’s whole-number part with the time’s fraction. Subtracting one value from another therefore gives elapsed time in days, including any fractional day.

For example, with a start of January 1, 2025 at 9:00 a.m. in A2 and an end of January 2, 2025 at 11:30 a.m. in B2, the difference is 1.1041667 days. That is one day plus 2.5 hours, or 26 hours and 30 minutes. Excel’s stored value is useful for calculation, but the cell’s number format determines how that value looks on screen.

This is why the same subtraction can appear as a decimal, a date, or a clock time depending on formatting. The underlying result has not changed; only its display has. Understanding that distinction helps prevent a common mistake: treating the displayed value as if it were a different calculation.

Calculate elapsed days

If both cells contain dates without times, subtraction returns the number of days between them. With the earlier date in A2 and the later date in B2, enter =B2-A2. For January 1 in A2 and January 10 in B2, the result is 9, because nine 24-hour intervals separate those dates.

Excel may initially display a date-like result instead of a plain number. Select the result cell, then choose Home > Number Format and select General or Number. You can also press Ctrl+Shift+~ on Windows to apply the General format. On Mac, use Control+Shift+~ if that shortcut is available in the keyboard layout; otherwise choose General from the Home tab’s Number Format menu.

Elapsed days are not always the same as the number of calendar dates counted inclusively. If a schedule should count both the first and last date, then with the first date in A2 and the last date in B2, use =B2-A2+1. For January 1 through January 10, that returns 10. Use this only when the business rule includes both endpoints, such as counting calendar days of a booking; ordinary elapsed-time calculations do not add one.

Show hours and minutes, including totals over 24 hours

For a duration that should be displayed as hours and minutes, subtract the timestamps and apply a duration format. With the start timestamp in A2 and the end timestamp in B2, enter =B2-A2, then open Format Cells with Ctrl+1 on Windows (Command+1 on Mac), choose Number > Custom, and enter [h]:mm in the Type box. The example from January 1 at 9:00 a.m. to January 2 at 11:30 a.m. displays as 26:30.

The square brackets around h matter. A plain format such as h:mm displays the hour portion on a 12- or 24-hour clock and rolls over after 24 hours. It could display 26 hours and 30 minutes as 2:30, hiding a full day. The bracketed format accumulates hours instead, so it can show 26:30, 52:15, or another total duration.

To display seconds as well, use the custom format [h]:mm:ss. For instance, with the same timestamp layout but an end time of January 2 at 11:30:45 a.m., the elapsed result displays as 26:30:45. Changing the format does not round or alter the stored result; it controls which parts of the duration are visible.

Convert a duration to decimal hours or minutes

Because a day is stored as 1, multiply a duration by 24 to convert it to hours. With the start timestamp in A2 and end timestamp in B2, enter =(B2-A2)*24. For the 26-hour-30-minute example, the result is 26.5 hours. Format the result as General or Number if Excel shows a time instead of a decimal.

To convert the same elapsed time to minutes, multiply by 1,440, the number of minutes in 24 hours. With the same start and end cells, enter =(B2-A2)*1440. The example returns 1,590 minutes. For decimal seconds, multiply the elapsed days by 86,400; that is 24 hours × 60 minutes × 60 seconds. These conversions are convenient for payroll calculations, service intervals, and averages, but apply any required rounding separately and according to the relevant policy.

A decimal-hour answer and an hours-and-minutes display are different representations of the same duration. For example, 26.5 hours means 26 hours and 30 minutes. Do not read the digits after the decimal point as minutes: 26.30 decimal hours would equal 26 hours and 18 minutes, not 26 hours and 30 minutes.

Handle times that cross midnight

If each cell contains a full date and time, ordinary subtraction works across midnight. For example, January 5 at 10:00 p.m. to January 6 at 2:00 a.m. is four hours. The dates tell Excel that the end belongs to the following day, so there is no need for a special overnight formula.

Sometimes a worksheet contains clock times only, with no dates—for example, 10:00 p.m. in A2 and 2:00 a.m. in B2. Direct subtraction produces a negative value because Excel reads the end time as earlier in the same day. If the intended rule is that an earlier end time means the next day, then with the start time in A2 and end time in B2 use =MOD(B2-A2,1). Format the result as [h]:mm; this example returns 4:00.

Use that MOD approach only when times without dates are meant to represent a single overnight interval. It wraps negative differences into a positive part of one day, so it can conceal an incorrectly entered end time or erase a genuine multi-day interval. When a shift can last more than one day, record full dates and times instead.

Get whole days, months, or years

Subtracting dates gives elapsed days, including the fraction caused by times. If you need only the whole number of complete days, use INT on the difference. With a start timestamp in A2 and an end timestamp in B2, enter =INT(B2-A2). The January 1 at 9:00 a.m. to January 2 at 11:30 a.m. example returns 1 complete day. This assumes the end is not earlier than the start; INT rounds negative numbers down, which may not match every desired rule.

For calendar age or completed month/year intervals, Excel’s DATEDIF function can be useful. With date-only values in A2 and B2, where A2 is the earlier date, enter =DATEDIF(A2,B2,"m") to count complete months. For January 15 to March 14, it returns 1, because a full two months have not elapsed. DATEDIF is useful for completed intervals, but it does not report a fractional month or the number of calendar month boundaries crossed.

With the same date-only layout, =DATEDIF(A2,B2,"y") counts complete years, while =DATEDIF(A2,B2,"d") counts days. The unit codes are text values in quotation marks. DATEDIF is an older compatibility function, and Excel does not offer it in the Function Arguments list in the same way as many newer functions; enter the formula directly. For ordinary elapsed time in hours or minutes, subtraction is simpler and preserves the time portion.

Check input order, blanks, and errors

For a start in A2 and end in B2, if the end is earlier than the start, the subtraction is negative. In Excel’s default 1900 date system, negative date/time results may display as #### under date or time formats. First check that the dates and times are entered correctly and in the intended order. A negative number format or General format can make the sign visible, but it does not resolve whether a negative duration is meaningful for the task.

If an end date may be missing, an explicit check can avoid showing a misleading result. With the start in A2 and the end in B2, enter =IF(B2="","",B2-A2). It leaves the result blank until B2 has a value. If you also want a message when the end is before the start, use =IF(B2="","",IF(B2<A2,"Check order",B2-A2)) with the same layout. This formula returns “Check order” for a reversed interval and otherwise returns the elapsed time; format the result column as needed. Since it can return either text or a number, avoid using that mixed-result column in calculations without accounting for the text.

Text that looks like a date may not be recognized as a usable Excel date, particularly when data comes from a system with a different regional date order. If subtraction returns #VALUE!, inspect the source cells, confirm that they contain numeric date/time values rather than text, and check for inconsistent formats such as day/month versus month/day. Applying a date format alone changes appearance, not text into a real date.

Choose the right result for your task

Before building a formula, decide what “difference” means in the context of the worksheet. A timesheet may need total hours; a report may need whole elapsed days; an age calculation usually needs completed years. These are related but not interchangeable calculations.

Use subtraction and a Number format for elapsed days as a decimal.
Use subtraction with [h]:mm for accumulated hours and minutes.
Multiply the difference by 24 or 1,440 when a decimal number of hours or minutes is required.
Use DATEDIF for completed calendar years or months, not as a general replacement for elapsed-time subtraction.
Use an inclusive count only when the rule explicitly counts both the start date and end date.

For work schedules, also decide whether the result should include breaks, weekends, or holidays. Basic subtraction counts the entire elapsed interval; it does not know which hours are working hours or which dates are nonworking days. Those requirements need additional logic and a clear definition of the schedule. Likewise, if values are timestamps from systems in different time zones, normalize them to the same time zone before subtracting; an ordinary Excel date/time value does not itself store time-zone information.

Frequently Asked Questions

Why does Excel show a date instead of the number of days?
Excel may have applied a date format to the result. The subtraction can still be correct. Change the result cell to General or Number to display elapsed days as a number.

How do I show more than 24 hours in Excel?
Use a custom number format with square brackets, such as [h]:mm. Without the brackets, the hour display can roll over at 24 hours and make a long duration look shorter.

Can Excel calculate a time difference across midnight?
Yes. Use full date-and-time values and subtract the start from the end. If you have clock times only and intend an overnight interval, MOD can wrap the difference, but it cannot distinguish an overnight shift from an invalid entry.

How do I calculate the difference in hours?
Subtract the start timestamp from the end timestamp and multiply by 24. Format the result as a number to show decimal hours; use a bracketed hour format instead if you want hours and minutes.

Why does the result show #### or #VALUE!?
Hashes can indicate a negative date/time result or a column too narrow to display the formatted value. #VALUE! often means a source value is text rather than a recognized date or time. Check the order, cell contents, and format before changing the formula.

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.