Employee tenure in years and months with DATEDIF

How it works, step by step

1. 01B2 holds the hire date, 15-Aug-2021, and TODAY() returns the current date, 14-Nov-2026 in this example.
2. 02DATEDIF(B2,TODAY(),"Y") counts the complete years between the two dates: 5.
3. 03DATEDIF(B2,TODAY(),"YM") counts the months left over after those complete years: 2.
4. 04The & operator joins the numbers with labels, giving 5 yrs 2 mos.
5. 05Use "M" for total complete months (62) or "D" for total days when you need a single number for sorting.

The same logic in four tools

=DATEDIF(B2,TODAY(),"Y")&" yrs "&DATEDIF(B2,TODAY(),"YM")&" mos"

=DATEDIF(B2,TODAY(),"Y")&" yrs "&DATEDIF(B2,TODAY(),"YM")&" mos"

SELECT name,
       DATE_PART('year', AGE(CURRENT_DATE, hire_date)) AS tenure_years,
       DATE_PART('month', AGE(CURRENT_DATE, hire_date)) AS tenure_months
FROM employees;

import pandas as pd

today = pd.Timestamp.today().normalize()
hire = pd.to_datetime(employees["hire_date"])
months = (
    (today.year - hire.dt.year) * 12
    + (today.month - hire.dt.month)
    - (today.day < hire.dt.day).astype(int)
)
employees["tenure_years"] = months // 12
employees["tenure_months"] = months % 12

Mistakes that break it

• !DATEDIF is undocumented in Excel's function list. It works in every version but does not appear in autocomplete, so you have to type it in full.
• !If the start date is later than the end date, DATEDIF returns #NUM!. Check for future hire dates or swapped columns.
• !Microsoft flags the "MD" unit (days, ignoring months and years) as unreliable. It can return negative or wrong values, so avoid it.
• !Hire dates stored as text give #VALUE!. In pandas, convert with pd.to_datetime(employees["hire_date"]) before using the .dt accessor.

Common questions

How do I calculate years of service in Excel?

Use =DATEDIF(B2,TODAY(),"Y") for complete years. For a decimal value such as 5.25, use =YEARFRAC(B2,TODAY()).

Does DATEDIF work in Google Sheets?

Yes. Google Sheets supports DATEDIF with the same units (Y, M, D, YM, YD, MD) and, unlike Excel, lists it in its function help.

What is the SQL equivalent of DATEDIF?

In Postgres, AGE(end_date, start_date) returns an interval, and DATE_PART('year', ...) and DATE_PART('month', ...) pull out the years and leftover months. SQL Server's DATEDIFF counts calendar boundaries crossed, so it can overstate tenure by one year.