Excel Date Difference Formulas for Days, Months, and Years

To find the number of elapsed days between two dates in Excel, subtract the start date from the end date: if the start date is in A2 and the end date is in B2, enter =B2-A2. For complete calendar months or years, use Excel’s DATEDIF function instead; the right formula depends on what you mean by “difference.”

How Excel calculates the difference between dates

Excel stores recognized dates as serial numbers, so subtracting one date from another gives the number of days between them. For example, if A2 contains January 1, 2024, and B2 contains January 10, 2024, the subtraction returns 9. This is elapsed time: it does not count the start date as a full day in addition to the days that pass.

Dates must be real Excel date values for subtraction to work as intended. A cell may look like a date but contain text instead, particularly if the value was imported or entered in a format Excel does not recognize under your regional settings. If subtraction returns #VALUE!, check whether both cells contain dates rather than text.

The result is a number, even if Excel displays it in a date format. If the result appears as another calendar date, select the result cell and choose Home > Number Format, then select General or Number. Excel for Mac and Excel for the web use the same date arithmetic and formulas; the ribbon layout may vary slightly by platform.

Choose a formula for days, months, or years

For straightforward elapsed days, subtraction is usually the clearest choice. For age, length of service, or the number of full months in a subscription, use DATEDIF with the unit that matches the question. It calculates completed units between a start date and an end date, not an approximate decimal duration.

Elapsed days: subtract the start date from the end date.
Complete months: use DATEDIF with the "m" unit.
Complete years: use DATEDIF with the "y" unit.
Working days: use NETWORKDAYS, optionally supplying a holiday range.
Elapsed hours: subtract date-time values, then format or convert the result appropriately.

For a concrete example, suppose A2 contains January 15, 2022, and B2 contains March 10, 2024, with both cells holding actual Excel dates. The elapsed-day calculation returns 785 days. That span includes two complete years and one additional complete month, so the completed-month calculation returns 25 and the completed-year calculation returns 2. Those results answer different questions; they are not competing versions of the same measurement.

Find complete months and years with DATEDIF

For elapsed calendar days, with the start date in A2 and the end date in B2, enter:

=B2-A2

For completed months between those same dates, with the start date in A2 and the end date in B2, enter:

=DATEDIF(A2,B2,"m")

For completed years between those same dates, with the start date in A2 and the end date in B2, enter:

=DATEDIF(A2,B2,"y")

In the January 15, 2022 to March 10, 2024 example, the month result is 25 because the 26th monthly anniversary, March 15, has not arrived. The year result is 2 because two full anniversaries have passed. This is why simply dividing days by 30 or 365 can produce an answer that does not match the number of complete calendar months or years.

DATEDIF also supports units that return the remaining months or days after counting full years or months. With the start date in A2 and the end date in B2, this formula returns the number of months after the last complete year:

=DATEDIF(A2,B2,"ym")

For the example dates, that result is 1. A combined display can show the complete years and remaining months together. With the start date in A2 and the end date in B2, enter:

=DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months"

The result is “2 years, 1 months.” If you need grammatically correct singular and plural labels, add an IF test or keep the numeric values in separate cells for reporting. Also note that DATEDIF may not appear in Excel’s formula autocomplete, but it is a real function supported for compatibility.

Count both the start and end dates

Subtracting dates measures the distance between them, so a period from Monday to Tuesday is one elapsed day. Sometimes a report instead needs to count every calendar date touched, including both endpoints—for example, a two-day event running from Monday through Tuesday. Add one to the ordinary date difference.

With the first date in A2 and the last date in B2, where both dates should be included in the count, enter:

=B2-A2+1

For January 1 through January 10, 2024, this inclusive count returns 10, while ordinary subtraction returns 9. Choose the inclusive version only when the question really asks how many dates are included. Adding one to every date calculation can overcount an elapsed duration, such as the time between a booking’s check-in and check-out.

Calculate weekdays and working days

For a work schedule that treats Saturday and Sunday as weekends, Excel’s NETWORKDAYS function counts weekdays between two dates and includes eligible start and end dates. It can also exclude a supplied list of holidays. Put the start date in A2, the end date in B2, and any holiday dates in C2:C10; enter:

=NETWORKDAYS(A2,B2,C2:C10)

If there are no holidays to exclude, with the start date in A2 and the end date in B2, enter:

=NETWORKDAYS(A2,B2)

For example, January 1 through January 5, 2024 contains five weekdays, so the second formula returns 5. If January 1 is listed as a holiday in the holiday range, the first formula returns 4. Make sure holiday entries are valid Excel dates; text that only looks like a date may not be excluded as expected.

If your weekend days are different—for instance, Friday and Saturday—use NETWORKDAYS.INTL and specify the weekend pattern. The weekend argument supports numeric codes and a seven-character pattern for Monday through Sunday, where 1 marks a nonworking day and 0 marks a working day. This is useful for shift schedules, but check the pattern carefully so the correct weekdays are being excluded. These functions count whole working days, not working hours or partial shifts.

Work with dates that include times

A date-time value includes a fractional part for the time of day. Subtracting two date-time values therefore returns a number of days that may include a fraction. If A2 contains 9:00 AM and B2 contains 5:30 PM on the same date, with both cells storing date-time values, the difference is one third of a day.

With the start date and time in A2 and the end date and time in B2, enter this formula to display the interval as hours and minutes:

=B2-A2

Format the result cell with the custom number format [h]:mm to show 8:30. The square brackets allow hours to accumulate beyond 24, which matters for longer intervals. A standard time format can wrap around and show only the clock time after a full day has passed.

To return decimal hours instead, with the start date-time in A2 and the end date-time in B2, enter:

=(B2-A2)*24

For 9:00 AM to 5:30 PM, the result is 8.5 hours. This is an elapsed-time calculation, not a payroll rule: it does not subtract unpaid breaks, apply overtime policies, or account for a work calendar. Those requirements need additional logic based on the applicable schedule.

Common errors and date-difference misconceptions

The result shows as a date: The calculation may be correct but displayed with a date format. Change the result cell to General or Number.
The formula returns #VALUE!: One or both inputs may be text. Convert them to real dates, or check the import and regional date settings.
DATEDIF returns #NUM!: Its start date must be earlier than or equal to its end date. Check that the two references are in the intended order.
The month count seems one short: DATEDIF counts completed months. If the end date falls before the next monthly anniversary, that month is not complete.
The answer is off by one: Decide whether you want elapsed days or an inclusive count of calendar dates. Those are different counting conventions.
The answer changes unexpectedly: Check whether the cells include hidden time values. Two cells can display the same date while storing different times.

Be cautious with the "md" unit in DATEDIF, which asks for remaining days after counting complete months. Microsoft notes that this unit can produce incorrect results in some situations. For most ordinary date comparisons, subtraction is more dependable for total elapsed days, while "m" and "y" are useful for completed months and years.

Also, do not assume every month has 30 days or every year has 365. Calendar months vary in length, and leap years add a day. Excel’s date subtraction accounts for the actual dates in the workbook, while a conversion such as dividing by 30 answers an approximate-duration question instead.

Frequently Asked Questions

What is the simplest Excel formula to subtract two dates?
Put the earlier date in A2 and the later date in B2, then enter =B2-A2. The result is elapsed days. If Excel displays a calendar date instead of a number, change the result cell’s format to General or Number.

How do I calculate age in years in Excel?
With the birth date in A2 and the date to calculate age on in B2, enter =DATEDIF(A2,B2,"y"). It returns completed years, not a decimal age. For age as of today, use TODAY() as the end date, keeping in mind that the result updates as the current date changes.

How do I find the number of months between two dates?
With the start date in A2 and end date in B2, use =DATEDIF(A2,B2,"m") for complete months. It does not count a partial final month. If you need a rough duration in average months rather than complete calendar months, that is a different calculation and should be labeled accordingly.

Can Excel subtract dates that include times?
Yes. With the start date-time in A2 and end date-time in B2, =B2-A2 returns the elapsed interval in days, including fractions of a day. Format the result as [h]:mm for cumulative hours and minutes, or multiply by 24 for decimal hours.

Why does my date difference show a negative number?
Subtraction returns a negative result when the date in the first reference is later than the date in the second. For DATEDIF, the start date must not be after the end date. Check the cell order and the actual stored dates before changing the formula.

Which date-difference method should you use?

Start by stating the question in plain language: elapsed days, included calendar dates, completed months, completed years, weekdays, or exact hours and minutes. Then choose the calculation that matches it. Subtraction is transparent for elapsed time; adding one handles an inclusive date count; DATEDIF handles complete calendar units; and NETWORKDAYS handles standard working-day counts.

Keep the start and end dates in separate cells when possible. That makes formulas easier to audit, lets you reuse the dates in other calculations, and makes it simpler to identify reversed or invalid entries. If results feed a report or deadline, label whether the number is elapsed or inclusive and whether weekends or holidays are excluded. A clearly defined counting rule is just as important as the formula itself.

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.