Months Between Two Dates in Excel and Google Sheets
Purpose
Calculating how many months are between two dates is a question that comes up in almost every HR, finance, and…
Calculating how many months are between two dates is a question that comes up in almost every HR, finance, and operations spreadsheet I work with. Employee tenure in months. Months remaining on a contract. Duration of a project phase. Age in months for a subscription billing calculation. The DATEDIF function handles this directly with the "M" unit argument and returns the count of complete elapsed months. The alternative formula using YEAR and MONTH arithmetic does not require DATEDIF and counts calendar month differences, which gives a slightly different result in some edge cases -- understanding which behaviour you need is the key to choosing the right approach. Both formulas return whole numbers. When a fractional month result is needed, a days-divided-by-average-month-length approach gives a decimal result. All three patterns are covered in the examples below.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=DATEDIF(start_date, end_date, "M")
Alternative without DATEDIF: =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)
Arguments
| Argument | Required | Description |
|---|---|---|
| start_date | Required | The earlier date. Must be a valid date value -- not a text string. Must be less than or equal to end_date. |
| end_date | Required | The later date. Must be greater than or equal to start_date. |
| unit | Required | "M" returns complete months elapsed. "YM" returns the months portion of the difference excluding full years. "MD" returns the remaining days after whole months are subtracted. |
How it works
DATEDIF(start,end,"M") calculates the number of complete calendar months from start to end. A complete month means the day number in the end date is at least equal to the day number in the start date -- if start is the 15th, the month increments when end reaches the 15th of the next month. The start date must be earlier than or equal to the end date or DATEDIF returns #NUM!. DATEDIF is not listed in Excel autocomplete -- type it manually -- but it works in all modern Excel versions and Google Sheets. The alternative formula (YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2) converts both dates to a month count (year times 12 plus month) and subtracts. This gives a calendar-month distance: January to March is always 2 regardless of the day of month. For subscription billing or HR tenure this may count one month too many or too few compared to the DATEDIF approach. For fractional months, divide the day difference by 30.44 (the average days per month).
Examples
1
Calculate each employee's tenure in complete months from their hire date to today.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Hire Date | Tenure (months) |
| 2 | Alice | 2022-01-15 | =DATEDIF(B2,TODAY(),"M") |
| 3 | Bob | 2023-06-01 | 22 |
| 4 | Carol | 2021-09-20 | 43 |
| 5 | Dan | 2024-03-10 | 13 |
| 6 | Eve | 2020-12-01 | 52 |
Row 2: 39
— Assuming today is April 2025: Alice was hired January 15 2022, which is 39 complete months ago. The result updates automatically every day because TODAY() recalculates on each file open.
DATEDIF counts complete months -- if today were January 14 2025, the result would be 35 not 36 because the 36th month is not yet complete. Replace TODAY() with a fixed end date when you need a static calculation.
2
Calculate the duration in months between contract start and end dates using the YEAR-MONTH arithmetic formula as an alternative to DATEDIF.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Contract | Start | End | Duration (months) |
| 2 | CTR-001 | 2024-01-01 | 2024-06-30 | =(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) |
| 3 | CTR-002 | 2023-07-15 | 2024-07-14 | 12 |
| 4 | CTR-003 | 2024-03-01 | 2024-09-01 | 6 |
| 5 | CTR-004 | 2022-01-01 | 2025-01-01 | 36 |
| 6 | CTR-005 | 2024-06-15 | 2025-06-14 | 12 |
Row 2: 5
— January to June is 5 calendar month steps (Jan=1, Jun=6, 6-1=5). The YEAR factor contributes 0 because both dates are in 2024. Contract CTR-001 spans January through June which is 5 complete calendar months by this method.
This formula gives calendar month distance, not complete elapsed months. CTR-001 starts January 1 and ends June 30 -- DATEDIF("M") would give 5 too in this case. But for January 31 to February 1, this formula returns 1 while DATEDIF returns 0 because only 1 day has elapsed, not a complete month.
3
Calculate a fractional month count to show tenure as a decimal -- useful for prorated salary or billing calculations.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Start | End | Months (decimal) |
| 2 | Alice | 2024-01-01 | 2024-04-15 | =ROUND((C2-B2)/30.44,1) |
| 3 | Bob | 2024-01-01 | 2024-07-01 | 6.0 |
| 4 | Carol | 2024-01-15 | 2024-04-30 | 3.5 |
| 5 | Dan | 2024-01-01 | 2024-02-15 | 1.5 |
| 6 | Eve | 2024-01-01 | 2025-01-01 | 12.0 |
Row 2: 3.4
— January 1 to April 15 is 105 days. 105 divided by 30.44 = 3.45, rounded to 1 decimal gives 3.4 months. Bob's exactly 6 calendar months gives 181 days divided by 30.44 = 5.95, shown as 6.0.
30.44 is the average number of days per month (365.25 divided by 12). This approach is approximate -- it will not always match exactly to a specific billing cycle definition, but it is the standard method for prorated calculations where a precise fractional month is needed.
Common use cases
1. Calculate employee tenure in complete months from hire date to today for an HR report
=DATEDIF(B2,TODAY(),"M")
2. Measure contract or subscription duration in months between fixed start and end dates
=DATEDIF(B2,C2,"M")
3. Count months remaining until a contract expiry or renewal date
=DATEDIF(TODAY(),C2,"M")
4. Calculate a decimal month count for prorated billing or salary calculations
=ROUND((C2-B2)/30.44,2)
5. Show months and remaining days combined using multiple DATEDIF units
=DATEDIF(B2,C2,"M")&" months "&DATEDIF(B2,C2,"MD")&" days"
Common errors
#NUM! error from DATEDIF
The start date is later than the end date. DATEDIF requires start_date <= end_date.
Fix: Swap the arguments or use IF to check order: =IF(B2<=C2,DATEDIF(B2,C2,"M"),"Invalid"). Alternatively use ABS with the MONTH arithmetic formula which handles reversed dates.
#NAME? error on DATEDIF
DATEDIF is not in the autocomplete list and the function name is misspelled. DATEDIF is a legacy function that must be typed manually.
Fix: Type DATEDIF exactly with no space and all caps. It does work in all modern Excel and Google Sheets versions despite not appearing in autocomplete.
Result is 0 when you expected 1
DATEDIF counts complete months -- if less than one full month has elapsed the result is 0. For example January 31 to February 28 is not a complete month if start day (31) > end day (28).
Fix: Use the MONTH arithmetic formula instead if you want calendar month distance regardless of day differences, or check whether partial months should contribute to the count.
Tips and variations
Use TODAY() as the end date for a live tenure calculation
=DATEDIF(B2,TODAY(),"M") always shows the current completed months from hire date. The result updates every time the file opens, making it the standard formula for any live tenure or age-in-months column.
=DATEDIF(B2,TODAY(),"M")
Combine "M" and "MD" units to show months and remaining days
=DATEDIF(B2,C2,"M")&" months "&DATEDIF(B2,C2,"MD")&" days" produces a readable duration label like 5 months 12 days. The "YM" unit also extracts the months portion within the current year for year-month-day breakdowns.
=DATEDIF(B2,C2,"M")&" months "&DATEDIF(B2,C2,"MD")&" days"
Wrap in IFERROR for rows where dates may be missing
If start or end date cells are sometimes blank, DATEDIF returns an error. Wrapping in IFERROR gives a clean empty or zero result: =IFERROR(DATEDIF(B2,TODAY(),"M"),"").
=IFERROR(DATEDIF(B2,TODAY(),"M"),"")
Excel vs Google Sheets
Excel vs Google Sheets
DATEDIF works in Excel 2003 and newer and all versions of Google Sheets. It is not listed in Excel autocomplete but functions correctly when typed manually. The YEAR-MONTH arithmetic alternative works in all versions of both applications. Behaviour of the "M" unit is identical in Excel and Google Sheets.
Related reading
Frequently asked questions
Use =DATEDIF(A2,B2,"M") where A2 is the start date, B2 is the end date, and "M" returns the number of complete months between them. Alternatively use =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2) which counts calendar month differences without requiring the DATEDIF function. Both formulas ignore partial months and return whole numbers.
DATEDIF(start,end,"M") counts complete elapsed months -- if exactly 1 month and 29 days have passed, it returns 1 not 2. The formula (YEAR(end)-YEAR(start))*12+MONTH(end)-MONTH(start) counts calendar month differences regardless of the day of month -- January 31 to February 1 is 1 month by this formula even though only 1 day has passed. Choose based on whether you need complete months elapsed or calendar month distance.
Use DATEDIF(start,end,"M")+DATEDIF(start,end,"MD")/30 to add a fractional month based on remaining days. For a simpler decimal approach, calculate the total days between dates and divide by the average month length: =(B2-A2)/30.44 where 30.44 is the average number of days per month. Round the result to control the displayed precision.
Yes. DATEDIF is available in Google Sheets with identical syntax and behaviour to Excel. Both applications support the "M", "Y", "D", "YM", "YD", and "MD" unit arguments. Note that DATEDIF is a legacy function not listed in Excel autocomplete -- you must type it manually -- but it works reliably in all modern versions of both applications.
Use =DATEDIF(TODAY(),B2,"M") where B2 is the future date and TODAY() is the current date as the start. This returns the number of complete months until B2. If B2 is in the past, DATEDIF returns a #NUM! error because the start date must always be earlier than the end date. Wrap in IFERROR or use IF(B2>TODAY(),DATEDIF(TODAY(),B2,"M"),0) to handle past dates.