Excel Formula for Time Between Dates

To find the time between two dates in Excel, subtract the earlier date or date-time from the later one: if the start is in A2 and the end is in B2, enter =B2-A2. Format the result as a number for days, or as elapsed time such as [h]:mm when you need total hours and minutes.

The basic date-difference formula

Excel stores dates as serial numbers, so subtracting one valid date from another returns the number of days between them. For example, if A2 contains January 5, 2026, and B2 contains January 8, 2026, the formula returns 3. The start date is not counted as a full day; this is ordinary date subtraction, not an inclusive count of calendar dates.

With the starting date in A2 and the ending date in B2, enter =B2-A2 in C2. In this example, C2 returns 3. If Excel displays the result as another date, change the result cell’s number format to General or Number. On Windows, select the cell and press Ctrl+1 to open Format Cells; on Mac, use Command+1. You can also use Home > Number Format and choose a numeric format.

For a whole-number count of date boundaries when the cells contain times as well as dates, with the start date-time in A2 and the end date-time in B2, use =INT(B2)-INT(A2). If A2 is January 5 at 11:00 p.m. and B2 is January 6 at 1:00 a.m., the result is 1 calendar date crossed, even though only two hours passed. Choose this calculation only when calendar-date boundaries matter more than elapsed duration.

How Excel stores dates and times

Excel represents a date as a whole-number serial value and a time as a fraction of a 24-hour day. As a result, a date-time value contains both parts, and subtracting two date-times produces a fraction or whole-plus-fraction number of days. A result of 0.5 means 12 hours; a result of 1.25 means one day and six hours.

This is why the same subtraction formula can answer several questions: it calculates the elapsed interval, while the cell’s number format controls how that interval looks. Formatting does not change the underlying result. A value may appear as 2.27 days, 54:30, or a date-like value depending on its format, even though the stored calculation is the same.

For reliable subtraction, the cells must contain real Excel date or date-time values, not text that merely looks like a date. If a date is left-aligned or cannot be changed by applying a date format, it may be stored as text. Re-enter it, convert it to a recognized date, or use a controlled import process. Date interpretation can depend on regional settings, so entries such as 03/04/2026 may be ambiguous.

Show elapsed days, hours, or minutes

For durations of less than a day, a time format such as h:mm is often easy to read. For intervals that may exceed 24 hours, use a bracketed elapsed-time format. With the start date-time in A2 and the end date-time in B2, enter =B2-A2; if the interval is from January 5, 2026 at 9:00 a.m. to January 7 at 3:30 p.m., the result is 2.270833 days, or 54 hours and 30 minutes. Format the result with the custom format [h]:mm to display 54:30.

Square brackets make the hours accumulate instead of restarting at zero after each 24-hour period. Without brackets, h:mm can show 6:30 for that same 54-hour-and-30-minute interval, which is misleading if you want the total duration. To show seconds as well, use [h]:mm:ss. In Format Cells, choose Custom and type the desired format code. These are display formats, not formulas.

If you need a decimal number of hours, with the start date-time in A2 and end date-time in B2, enter =(B2-A2)*24. The example above returns 54.5 hours. For decimal minutes, use =(B2-A2)*1440; it returns 3,270 minutes. These results are useful for calculations or summaries, while an elapsed-time format is usually clearer for a person reading a duration.

Choose the calculation that matches the question

“Time between dates” can mean elapsed time, whole days, calendar days touched, or completed months and years. Decide what the result should represent before selecting a formula. The difference matters in scheduling, age calculations, service periods, and reports that count workdays.

Elapsed days and fractions of a day: subtract the start from the end. A duration of 36 hours is 1.5 days.
Calendar-date boundaries crossed: remove the time parts with INT before subtracting. A short interval across midnight can count as one date boundary.
Whole completed years or months: use DATEDIF, rather than dividing the day count by 365 or 30. Months and years have different lengths.
Working days: use NETWORKDAYS when weekends and possibly listed holidays should be excluded.

To count completed years between date-only values in A2 and B2, enter =DATEDIF(A2,B2,"y"). If A2 is June 15, 2010, and B2 is June 14, 2026, the result is 15 because the sixteenth anniversary has not yet arrived. DATEDIF is a legacy function that remains available in Excel; it may not appear in formula autocomplete. Its start date must not be later than its end date.

For completed months after the full years have been counted, with the same date layout, use =DATEDIF(A2,B2,"ym"). For the June 15, 2010 to June 14, 2026 example, the result is 11 remaining months. This unit is not a total-month count; it returns the month remainder after complete years. For a total number of completed months, with dates in A2 and B2, use =DATEDIF(A2,B2,"m"). It returns 191 for that example.

Count working days and exclude holidays

Subtracting dates counts every calendar day, including Saturdays, Sundays, and holidays. If the question is how many standard Monday-to-Friday workdays fall in a period, NETWORKDAYS is designed for that count. It counts both the start and end dates when they are working days, so its result is an inclusive workday count—not an elapsed duration in 24-hour units.

With a start date in A2 and an end date in B2, enter =NETWORKDAYS(A2,B2). From Monday, January 5, 2026, through Friday, January 9, 2026, it returns 5. To exclude holidays listed in D2:D10 as well, with the same start and end cells, use =NETWORKDAYS(A2,B2,D2:D10). Keep the holiday cells as actual Excel dates; blank cells in the supplied range are ignored.

NETWORKDAYS uses Saturday and Sunday as weekend days by default. For a nonstandard weekend pattern, Excel also provides NETWORKDAYS.INTL, which lets you specify weekend days. Check the function’s weekend argument carefully before using it, because a wrong weekend pattern changes the count. Neither working-day function measures hours worked or accounts for a company’s shift schedule by itself.

Time-only entries and overnight intervals

If cells contain times without dates, subtracting them works when the ending time is later on the same day. For example, with the start time 9:00 a.m. in A2 and 5:30 p.m. in B2, =B2-A2 returns eight hours and thirty minutes. Format the result as h:mm to show 8:30.

An overnight shift can make a simple subtraction negative. If A2 contains 10:00 p.m. and B2 contains 2:00 a.m. as time-only values, then, with those cells in place, use =MOD(B2-A2,1). The result is four hours; format it as h:mm. MOD wraps the difference into a single 24-hour day, so it is appropriate for a same-shift overnight interval but not for date-time values where the actual number of days matters.

For shifts that may last longer than one day, enter full date-time values at both ends and use ordinary subtraction. A complete start and end date removes the ambiguity about whether the end time belongs to the same day or a later day.

Common errors and how to fix them

The result looks like a date: the formula may be correct, but the result cell has a date format. Change it to General, Number, or an elapsed-time format.
The total hours appear to restart at 24: replace h:mm with the custom elapsed format [h]:mm.
The result is negative or shows hash marks: check that the end is not earlier than the start. Excel may not display negative date-time values in the default date system; a number format cannot make an invalid interval positive.
The formula gives an unexpected result: confirm both inputs are real date-time values and check for hidden times. A cell displayed as a date may still contain a time fraction.
The dates appear reversed: check regional date order and whether dates were imported as text. Explicitly converting or re-entering ambiguous values can prevent silent misinterpretation.

For a duration that should never be negative, it may be tempting to reverse the subtraction or use an absolute value. That can hide a data-entry error. Decide whether reversed dates are valid in the situation; if they are not, investigate the source rather than automatically masking the sign.

Frequently Asked Questions

What is the simplest Excel formula to subtract two dates?
With the start date in A2 and end date in B2, use =B2-A2. The result is the number of days between the values; format it as Number or General if Excel displays it as a date.

How do I calculate hours between two dates in Excel?
With start date-time in A2 and end date-time in B2, use =(B2-A2)*24 for decimal hours. For hours and minutes, use the subtraction result and format the cell as [h]:mm.

How do I calculate months between two dates?
With the earlier date in A2 and later date in B2, =DATEDIF(A2,B2,"m") returns completed months in total. Use the “ym” unit instead when you want the remaining months after whole years.

Why does Excel show ###### for my time difference?
The value may be negative, or the column may be too narrow for the selected format. Check the order of the dates first, then widen the column. A wider column will not resolve a negative date-time display limitation.

Does Excel count the start date in a date difference?
Simple subtraction returns the difference between date values, so January 5 to January 8 is 3 days, not 4. If you need an inclusive count of calendar dates, add 1 to the date-only difference; for inclusive working days, NETWORKDAYS already counts eligible endpoints.

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.