ThisCalculator

Everyday

Excel Calculate Weeks, Years, and Months Between Dates

Updated · Standard formula, shown below

Excel Calculate Weeks, Years, and Months Between Dates

Excel can calculate the time between two dates as days, whole months, whole years, or weeks, but the right formula depends on whether you want complete calendar units or a decimal estimate. Enter the start and end dates in cells, then use formulas such as subtraction, DATEDIF, or a day count divided by seven.

Choose the kind of date difference

Before choosing a formula, decide what “between” means for your task. Excel stores dates as serial numbers, so subtracting one date from another returns the number of elapsed days. That is different from counting calendar boundaries or reporting only completed months or years.

  • Elapsed days: the exact number of days from one date to the other.
  • Elapsed weeks: days expressed as weeks, either with a decimal or as complete seven-day periods.
  • Completed months or years: whole calendar anniversaries reached by the end date.
  • Calendar months or years: a count of date boundaries crossed, which can differ from completed anniversaries.

For example, a period from January 31 to February 28 is 28 days, but it is not necessarily one completed month under an anniversary-based calculation. Being clear about the meaning prevents a formula from giving a technically correct but unsuitable answer.

Calculate elapsed days and weeks

Put the earlier date in A2 and the later date in B2. To return the elapsed number of days, enter:

=B2-A2

Format the result cell as General or Number if Excel displays it as a date. Date subtraction works because Excel dates are stored as day numbers. If you want to count both the start and end date as included days, use =B2-A2+1. That inclusive count is useful for some schedules, but it is not the usual elapsed-time difference.

For weeks, divide the day difference by seven:

=(B2-A2)/7

This returns weeks as a decimal, so 17 days becomes about 2.43 weeks. To show only complete seven-day periods, use =QUOTIENT(B2-A2,7) or =INT((B2-A2)/7) when dates are in chronological order. For a rounded decimal result, use =ROUND((B2-A2)/7,2).

There is no single universal definition of a calendar week difference. Dividing by seven measures elapsed blocks of seven days; counting Mondays, ISO weeks, or week numbers crossed is a separate calendar calculation.

Calculate completed months and years with DATEDIF

Use DATEDIF when you need the number of complete calendar units between dates. Its syntax is =DATEDIF(start_date,end_date,unit). With the start date in A2 and end date in B2, common formulas are:

  • =DATEDIF(A2,B2,"m") — complete months.
  • =DATEDIF(A2,B2,"y") — complete years.
  • =DATEDIF(A2,B2,"d") — elapsed days.
  • =DATEDIF(A2,B2,"ym") — remaining months after complete years are removed.
  • =DATEDIF(A2,B2,"md") — remaining days after months and years are removed.

For example, if A2 is January 15, 2022 and B2 is April 20, 2024, DATEDIF with “y” returns 2, while “m” returns 27. The combined age-style result can be assembled as =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&" months, "&DATEDIF(A2,B2,"md")&" days". The result is text, so use numeric formulas instead if you need to sort or calculate with the components.

DATEDIF is an older compatibility function and may not appear in Excel’s formula suggestions, but it is supported in common Excel versions. Its units are enclosed in quotation marks, and the start date must not be later than the end date.

Calendar months and fractional years

DATEDIF with “m” counts completed months, not every month boundary touched. If you instead need the difference between the year-and-month labels, use =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2). This gives the number of calendar month transitions, regardless of the days of the month. For January 31 to February 1, it returns 1 even though only one day elapsed and no full month passed.

For an approximate number of years with a decimal, divide elapsed days by 365.25: =(B2-A2)/365.25. The 365.25 divisor approximates the average Gregorian year over a leap-year cycle; it does not produce an exact calendar-year interval. Excel’s =YEARFRAC(A2,B2) can also return a year fraction, but its optional basis changes the day-count convention. Check that convention before comparing results with another system.

Format results and handle input dates

A formula can be right while its displayed result looks wrong. If subtracting dates shows another date, change the result cell’s number format to General or Number. If a month or year formula shows a decimal, check that you used the intended function and unit.

  • Enter valid Excel dates, rather than text that only looks like a date. Regional settings can affect whether entries such as 03/04/2024 mean March 4 or April 3.
  • Use cell references or DATE(year,month,day) to avoid ambiguous typed dates. For example, =DATE(2024,3,4) creates March 4, 2024.
  • Check that the end date is on or after the start date. Reversed dates produce negative subtraction results, while DATEDIF can return an error.
  • For timestamps, subtraction returns a fraction of a day as well as whole days. Multiply by 24 for hours or 1440 for minutes, and consider whether the time of day should count.

Common mistakes and limitations

The most frequent mistake is treating every month as 30 days or every year as 365 days. Months vary in length, and leap years add a day, so dividing a day count by 30 or 365 is only an estimate. Use DATEDIF for completed calendar months and years; use a stated day-count convention when a decimal fraction is required.

Another frequent error is assuming “weeks between” always means the number of calendar weeks touched. A period of seven days can cross one or two week-number boundaries depending on its starting weekday. For elapsed weeks, divide the date difference by seven. For week-number or Monday-to-Sunday reporting, define the week start and year convention separately.

Finally, Excel date systems and imported data can create surprises. Dates pasted from another application may be text, and workbooks can use different date systems. If subtraction returns an error or an implausible result, verify the cell values and formats before changing the formula.

Frequently Asked Questions

What formula calculates the number of weeks between two dates in Excel?
Use =(B2-A2)/7 for decimal weeks. Use =QUOTIENT(B2-A2,7) for complete seven-day periods.

How do I calculate years and months between dates?
Use =DATEDIF(A2,B2,"y") for complete years and =DATEDIF(A2,B2,"ym") for leftover months after those years.

Why does DATEDIF show an error?
Usually the start date is later than the end date, one value is stored as text rather than a valid date, or the unit argument was entered incorrectly. Check the dates and use a quoted unit such as "m".

Does DATEDIF count the first and last date?
No. It reports the difference between date values, not an inclusive count of dates. For inclusive days, subtract the start from the end and add 1.

How can I get months as a decimal?
There is no one exact decimal-month convention because months have different lengths. You can use elapsed days divided by a chosen average, such as 30.4375 days, but label the result as an estimate; use DATEDIF for whole completed months.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.