Calculate Age and Tenure from Dates with pandas (DATEDIF Equivalent)

"How old is each customer?" and "how long has each employee been with us?" sound like subtraction. In Excel they are DATEDIF(start, end, "Y"), a function so undocumented that people copy it from forum posts. In pandas the usual first attempt is (end - start).days / 365.25, which is right most of the time and wrong on exactly the days that matter — birthdays and work anniversaries, when the count should tick over. This page, part of Working with Dates and Times in Excel Data, computes completed years, months and days for whole columns, matching DATEDIF, and covers the 29 February case, fractional years and tenure bands.

Prerequisites

pip install pandas openpyxl python-dateutil

staff.xlsx has six employees with birth and hire dates, including two born on 29 February, one whose birthday is the report date and one whose birthday is the day after it.

import pandas as pd

staff = pd.read_excel("staff.xlsx")
AS_OF = pd.Timestamp("2026-09-25")

Fixing the report date in AS_OF, rather than using today, makes every run of the report give the same answer.

Step 1 — See the days-divided-by-365.25 problem

naive = ((AS_OF - staff["birth_date"]).dt.days / 365.25).astype(int)
print(pd.DataFrame({"employee": staff["employee"], "birth_date": staff["birth_date"].dt.date, "naive_age": naive}))

Dev was born on 25 September 2001 and turns 25 on the report date, but the naive formula says 24: the 9,131 days include six leap days, and 9,131 / 365.25 is 24.9993. Dividing by 365 fails the other way, making people a year older a few days early. No constant divisor is right, because leap days are not spread evenly across a lifetime.

Step 2 — Count completed years by comparing month and day

A person has completed another year once the end date's month and day have reached the start date's. That is DATEDIF's "Y" rule, and it vectorises neatly:

def completed_years(start, end):
    years = end.year - start.dt.year
    not_yet = (end.month < start.dt.month) | ((end.month == start.dt.month) & (end.day < start.dt.day))
    return years - not_yet.astype(int)

staff["age"] = completed_years(staff["birth_date"], AS_OF)
print(staff[["employee", "birth_date", "age"]].to_string(index=False))

Dev is 25 and Ben, whose birthday is tomorrow, is still 35. These results were checked against DATEDIF(A1, B1, "Y") recalculated in LibreOffice for every row, and they agree. When the end date varies per row — tenure until each employee's leaving date, say — pass a Series and use .dt on both sides.

Step 3 — Months and the remainder, like DATEDIF "M" and "YM"

DATEDIF's "M" gives completed months and "YM" the months left over after whole years — together they produce "36 years, 0 months". The same comparison, applied at month level, gives both:

def completed_months(start, end):
    months = (end.year - start.dt.year) * 12 + (end.month - start.dt.month)
    return months - (end.day < start.dt.day).astype(int)

staff["tenure_months"] = completed_months(staff["hire_date"], AS_OF)
staff["tenure_y"] = staff["tenure_months"] // 12
staff["tenure_m"] = staff["tenure_months"] % 12
staff["tenure"] = staff["tenure_y"].astype(str) + " y " + staff["tenure_m"].astype(str) + " m"
print(staff[["employee", "hire_date", "tenure_months", "tenure"]].to_string(index=False))

Finn, hired in January 2026, shows 0 y 8 m. A hire date in the future — a signed contract that has not started — would give a negative count; filter or flag such rows rather than reporting negative tenure.

Step 4 — Decide the 29 February rule

Cleo and Finn were born on 29 February. In a non-leap year, is their birthday 28 February or 1 March? The rule above, like DATEDIF, says 1 March: on 28 February they have not yet reached "month 2, day 29". dateutil.relativedelta, a common Python alternative, says 28 February:

from dateutil.relativedelta import relativedelta

leap_born = pd.Series(pd.to_datetime(["2004-02-29"]))
for day in ["2027-02-28", "2027-03-01"]:
    end = pd.Timestamp(day)
    rd = relativedelta(end.to_pydatetime(), leap_born[0].to_pydatetime()).years
    print(day, "month/day rule:", completed_years(leap_born, end)[0], "| relativedelta:", rd)

On 28 February 2027 the month/day rule gives 22 and relativedelta 23; from 1 March both give 23. Both conventions are used in law and in payroll systems, so this is a question for whoever owns the policy, not a bug to fix. What matters is that one convention is applied everywhere — mixing DATEDIF in a workbook with relativedelta in a script produces reports that disagree for a day each year.

Step 5 — Fractional years for pro-rating

Pro-rated benefits and service awards sometimes need tenure as a decimal, like Excel's YEARFRAC. A simple and transparent version adds completed years to the fraction of the current year elapsed:

def years_fraction(start, end):
    whole = completed_years(start, end)
    last = pd.to_datetime({"year": start.dt.year + whole, "month": start.dt.month,
                           "day": start.dt.day.where(~((start.dt.month == 2) & (start.dt.day == 29)), 28)})
    nxt = pd.to_datetime({"year": start.dt.year + whole + 1, "month": start.dt.month,
                          "day": start.dt.day.where(~((start.dt.month == 2) & (start.dt.day == 29)), 28)})
    return whole + (end - last).dt.days / (nxt - last).dt.days

staff["tenure_years"] = years_fraction(staff["hire_date"], AS_OF).round(3)
print(staff[["employee", "hire_date", "tenure_years"]].to_string(index=False))

Each year's fraction is measured against that year's actual length, so the result increases smoothly and hits a whole number exactly on each anniversary. Excel's YEARFRAC has five "basis" conventions (actual/actual, 30/360 and others) that give slightly different decimals; if the output must match a workbook's YEARFRAC to the last digit, recalculate the workbook and compare, or state the basis in the report.

Step 6 — Group tenure into bands

Reports usually want bands rather than exact values — under one year, one to three, three to five, five to ten, ten and over. pd.cut on completed years makes the bands explicit and keeps their order:

bins = [0, 1, 3, 5, 10, 100]
labels = ["<1 year", "1-3 years", "3-5 years", "5-10 years", "10+ years"]
staff["band"] = pd.cut(staff["tenure_y"], bins=bins, labels=labels, right=False)
print(staff[["employee", "tenure", "band"]].to_string(index=False))
print(staff["band"].value_counts(sort=False).to_dict())

right=False makes each band include its lower edge, so exactly three years falls in "3-5 years" rather than "1-3 years". Bin Values into Ranges with pandas cut covers band edges in detail.

Put DATEDIF next to the results for checking

When a workbook will be reviewed by people who trust Excel more than Python, write the pandas results and a DATEDIF formula side by side. Anyone can see the two agree, and the formula keeps working if they change a date:

from openpyxl import load_workbook

out = staff[["employee", "birth_date", "hire_date", "age", "tenure_months"]]
out.to_excel("staff_ages.xlsx", index=False)
wb = load_workbook("staff_ages.xlsx")
ws = wb.active
ws["G1"], ws["F1"] = "as_of", "age_check"
ws["G2"] = AS_OF.to_pydatetime()
for row in range(2, ws.max_row + 1):
    ws[f"F{row}"] = f'=DATEDIF(B{row},$G$2,"Y")'
wb.save("staff_ages.xlsx")
print(ws["F2"].value)

Recalculated, the age_check column matches age row for row — which is how the rule in Step 2 was verified. Keep the check column in review copies and drop it from the final report, or keep it permanently if readers like to see the working.

Common pitfalls

• Dividing days by 365.25. It is off by one on birthdays and anniversaries.
• Using today's date. Reports change when rerun; pass the report date explicitly, and print it in the report header.
• Two leap-day conventions in one system. Choose one and document it.
• Negative values. Future start dates produce negative ages or tenure; flag them.
• Dates stored as text. Parse them first; Parse Excel Dates into Python datetimes with pandas shows how.

Performance notes

All the functions here are vectorised: year, month and day components come from .dt accessors and the comparisons run on whole columns, so they handle millions of rows in well under a second. relativedelta is a per-row Python call and is best kept for single values or small tables.

Conclusion

Count completed years and months by comparing month and day, not by dividing days by 365.25 — the rule that DATEDIF uses and that the functions here implement for whole columns. Pass a fixed report date, decide once whether 29 February birthdays fall on 28 February or 1 March, use a year-length-aware fraction when tenure must be pro-rated, and group the results into ordered bands with pd.cut.

Frequently asked questions

Why not just divide the number of days by 365.25? Because leap years are not spread evenly, the result drifts across birthdays. On someone's 25th birthday, 9,131 days divided by 365.25 is 24.9993, which truncates to 24. Count completed years by comparing month and day instead.

How does this match Excel's DATEDIF? DATEDIF(start, end, "Y") counts completed years: the year difference, minus one if the end date's month and day come before the start date's. The pandas code on this page implements the same rule and was checked against DATEDIF results.

When does someone born on 29 February turn a year older? It depends on the convention. DATEDIF, and the rule on this page, count the birthday as reached on 1 March in non-leap years. dateutil's relativedelta counts it on 28 February. Pick the convention your organisation uses and apply it consistently.

How do I calculate tenure as of a report date rather than today? Pass the report date as the end date for every row instead of using today's date. Reports then give the same answer whenever they are rerun.

Related

• Up one level: Working with Dates and Times in Excel Data — dates from import to report.
• Calculate Business Days Between Excel Dates with pandas — working days rather than calendar ones.
• EOMONTH and EDATE Equivalents in pandas — moving dates by months.
• Group Excel Rows by Month and Quarter with pandas — calendar grouping.