Google Sheets DATEDIF: Calculate Days, Months, and Years

Google Sheets’ DATEDIF function calculates the time between two dates in whole days, months, or years. Enter a start date, an end date, and a unit such as "D" for days or "Y" for completed years; for example, with dates in A2 and B2, use =DATEDIF(A2,B2,"Y").

What DATEDIF does

DATEDIF is useful when you need elapsed time rather than a calendar display. You can use it to find someone’s age in completed years, measure an employee’s service period, calculate full months between billing dates, or count the days between two events. It returns a number, not a formatted phrase or a new date.

The function uses this pattern: DATEDIF(start_date,end_date,unit). The start date must come before or be the same as the end date. The unit is a text code in quotation marks. In Google Sheets, the codes are not case-sensitive, but uppercase is common because it makes them easy to spot.

DATEDIF counts completed intervals for its year and month units. For example, a period of 2 years and 11 months has 2 completed years, not 3. The day unit, by contrast, reports the number of elapsed days between the dates; it does not count both endpoints as separate days.

DATEDIF units and what they mean

Choose the unit according to the question you are trying to answer. The main options are:

"Y" returns the number of complete years between the dates.
"M" returns the total number of complete months, including months within completed years.
"D" returns the total number of elapsed days.
"YM" returns the remaining complete months after whole years are excluded.
"YD" returns the day difference after years are excluded. Because this is not the same as total elapsed days, take care when using it for reporting.
"MD" returns a day difference after months and years are excluded. Google cautions that this unit can produce a negative, zero, or inaccurate result, so it is usually safer to calculate a specific remainder another way if accuracy at month boundaries matters.

For a concrete comparison, suppose A2 contains January 15, 2022, and B2 contains June 10, 2024. With those dates, "Y" returns 2 completed years, "M" returns 28 completed months, and "D" returns 877 elapsed days. The remaining-month unit "YM" returns 4. These results answer different questions: 28 is the total full-month count, while 4 is the month remainder after the two full years are set aside.

How to enter the function in Google Sheets

Put valid date values in two cells, then enter the formula in an empty cell. For a typical layout, A2 holds the earlier date and B2 holds the later date. To count full years between them, enter =DATEDIF(A2,B2,"Y"). For the example dates above, the result is 2. To count complete months with that same layout, enter =DATEDIF(A2,B2,"M"); the result is 28. To count elapsed days, enter =DATEDIF(A2,B2,"D"); the result is 877.

Each formula uses the same two date cells; change only the unit to answer a different question. You can type a formula into a cell or select a cell and begin typing it in the formula bar. Google Sheets calculates the result when you press Enter. If you copy a formula down a column, the relative references adjust for each row, so A3 and B3 are used in the next row.

For a live age calculation, the layout can be a birth date in A2 and today’s date supplied by the spreadsheet. With the birth date in A2, use =DATEDIF(A2,TODAY(),"Y") to return completed years as of the current date. Because TODAY() updates, the result can change as the date changes. If you need an age as of a particular past or future date, enter that reference date in B2 and use =DATEDIF(A2,B2,"Y") instead; for a birth date of January 15, 2000, and a reference date of June 10, 2024, that formula returns 24.

Choosing between years, months, and days

Start by defining what “between” means in your use case. A project dashboard may need total elapsed days. A subscription report may need complete billing months. A staff list may need completed years of service. These are not interchangeable measurements, and a single pair of dates can legitimately produce different values for each unit.

Use "D" when you need a straightforward elapsed-day count, such as the number of days between an order and its delivery. Use "M" for a total count of full months, such as a tenure figure expressed as months. Use "Y" for whole completed years, such as an age or anniversary measure. Use "YM" when you specifically want the month component left over after full years are removed, often for a display such as “2 years, 4 months.”

For that kind of combined display, keep the calculations separate and label them clearly. With a start date in A2 and end date in B2, =DATEDIF(A2,B2,"Y") gives completed years, and =DATEDIF(A2,B2,"YM") gives the remaining complete months. For January 15, 2022, through June 10, 2024, the results are 2 and 4. The combination describes completed years and leftover full months; it does not include the remaining days.

Dates, formatting, and locale settings

DATEDIF works on date values, not on how dates happen to look on screen. Google Sheets stores recognized dates as numeric values and applies a date format to display them. You can change a cell’s display using Format > Number > Date, but changing the format does not repair text that Sheets has failed to recognize as a date.

Date interpretation can vary by spreadsheet locale. For example, a typed value with a slash may be interpreted as month/day/year in one locale and day/month/year in another. Check the spreadsheet’s locale under File > Settings if a date appears reversed or a formula returns an unexpected result. For data imported from another system, it is often safer to inspect the values and convert text to actual dates before calculating.

A useful check is to select each input cell and see whether Sheets treats it as a date value. If changing its number format to Date produces a sensible date, that is a good sign it is recognized. If the cell continues to display the original text, clean or convert the input first. Formatting alone cannot turn arbitrary text into a valid date.

Common errors and surprising results

The most frequent error is putting the later date first. DATEDIF expects the start date to be earlier than or equal to the end date; reversing the order can return an error. Check both source cells, especially when dates are imported, sorted, or calculated from other formulas.

Another common surprise is that a month is not treated as a fixed 30 days. DATEDIF’s "M" unit counts completed calendar months, so the result depends on the day of the month as well as the number of days elapsed. A short interval spanning a month boundary does not necessarily count as one complete month. This matters for month-end dates and for policies that define a billing period differently from calendar-month completion.

Also remember that a result of zero for "Y" does not mean there was no time between the dates; it means less than one complete year passed. Likewise, a result of zero for "M" can describe a period of several days or weeks. If a report needs a human-readable duration, show multiple components rather than treating a zero in one unit as a zero-length interval.

Other checks worth making include:

Confirm that both inputs are recognized dates and not text strings.
Check that the unit is enclosed in straight quotation marks and is one of the supported codes.
Verify that the start and end cells are in the intended order.
Decide how your organization handles leap-day birthdays and month-end anniversaries; a spreadsheet formula cannot decide that policy for you.
Avoid relying on "MD" for a critical result because its behavior can be unreliable around date boundaries.

DATEDIF compared with other date calculations

DATEDIF is convenient when the desired answer is a whole number of years, months, or days. If you subtract one valid date cell from another, Google Sheets returns the elapsed number of days as a numeric difference. That can be simpler for day counts, but it does not directly express completed months or years. DATEDIF makes those units explicit.

For calendar arithmetic—such as moving a date forward by a number of months—DATEDIF is not the right tool because it measures an interval rather than creating a new date. For a breakdown into calendar years, months, and days, use separate calculations and check how month ends should be treated. A phrase such as “2 years, 4 months, and 26 days” may sound intuitive, but the day remainder can depend on how the chosen method handles differing month lengths. Define the intended convention before building a report.

DATEDIF is also available in Excel, but this page’s menu paths and examples are for Google Sheets in a web browser. Excel’s interface and some locale details differ. In Sheets, use its formula bar and the Format menu described above; if a workbook is shared with Excel users, confirm that the same dates and results are being interpreted as intended in both programs.

Frequently Asked Questions

Does Google Sheets have a DATEDIF function?
Yes. Google Sheets supports DATEDIF(start_date,end_date,unit) for calculating whole days, months, or years between two dates. It may not be as prominent as some commonly suggested functions, but you can type it directly into a cell.

How do I calculate age in Google Sheets?
Put the birth date in A2 and use =DATEDIF(A2,TODAY(),"Y") to calculate completed years as of today. For age on a specific date, put that date in B2 and use =DATEDIF(A2,B2,"Y"). Decide how to handle a February 29 birthday in non-leap years if the result must follow a formal policy.

How do I get the total number of months between dates?
With the earlier date in A2 and the later date in B2, use =DATEDIF(A2,B2,"M"). This returns completed months in total, not a month remainder. For a remainder after full years, use the "YM" unit instead.

Why does DATEDIF return an error?
Check that the first date is not later than the second, that both cells contain recognized date values, and that the unit code is valid and quoted. If a date was imported as text or interpreted using a different locale, correct the input before troubleshooting the formula.

What is the difference between “M” and “YM”?
"M" gives all completed months across the full interval. "YM" removes completed years and returns only the remaining complete months. For a period of 2 years and 4 complete extra months, those units return 28 and 4 respectively.

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.