Ravindra BagaleCourses & study guides Track your progress

Guides

Calculate Age and Years of Service in Excel (DATEDIF, YEARFRAC)

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Shraddha in HR needs each employee's age and years of service on the report date, 6 October 2026. Ruhi was born on 15-07-2001. Subtracting years (2026 - 2001 = 25) is right here, but it would be wrong for someone whose birthday has not come yet this year. DATEDIF counts complete years, months and days correctly: 25 years, 2 months, 21 days.

DATEDIF(start, end, unit) counts the time between two dates. Unit "Y" gives complete years, "YM" the extra months after those years, and "MD" the extra days. YEARFRAC gives the same age as a decimal, and EDATE moves a date by whole months.

Download the practice file (date-functions-age-calculation.xlsx)

Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

  1. Put the report date in F1 (or use TODAY()).
  2. Age in years: =DATEDIF(B2,$F$1,"Y").
  3. Extra months: =DATEDIF(B2,$F$1,"YM").
  4. Extra days: =DATEDIF(B2,$F$1,"MD").
  5. Copy down for every employee.
=DATEDIF(B2,$F$1,"Y")
=DATEDIF(B2,$F$1,"Y")&" years "&DATEDIF(B2,$F$1,"YM")&" months"

The data and the answers (report date 06-10-2026)

NameDate of birthAge "Y""YM""MD"Service in years
Ruhi15-07-2001252212
Amir02-01-1990369411
Zoya06-10-199828000

Zoya's birthday is on the report date, so she has just turned 28: 0 extra months and 0 days. Her service (joined 01-04-2026) is 0 complete years and =DATEDIF(C4,$F$1,"YM") gives 6 months.

Show age as one sentence

=DATEDIF(B2,$F$1,"Y")&" years "&DATEDIF(B2,$F$1,"YM")&" months"

Result for Ruhi: 25 years 2 months. The & joins numbers and words into one text.

Age as a decimal with YEARFRAC

=YEARFRAC(B2,$F$1,1) gives about 25.23 for Ruhi. Wrap it in INT for complete years: =INT(YEARFRAC(B2,$F$1,1)) gives 25. The last argument 1 tells Excel to use real calendar days.

Other date formulas HR uses

QuestionFormulaRuhi's answer
Probation ends 6 months after joining=EDATE(C2,6)01-12-2024
Retirement at 60=EDATE(B2,60*12)15-07-2061
Last day of the joining month=EOMONTH(C2,0)30-06-2024
Days since joining=$F$1-C2857

Format EDATE and EOMONTH results as dates (Home > Number Format > Short Date); otherwise you see a serial number like 45627.

Good to know

  • DATEDIF does not appear in the formula suggestions, but it works in every Excel version. Type it fully.
  • If the start date is after the end date, DATEDIF shows #NUM!.
  • Use a fixed report date in a cell for reports you send. TODAY() changes every day, so last month's report would change too.

Ravindra Bagale's Tip

For age, never use =(TODAY()-B2)/365. Leap years make it drift, and someone turns a year older a day early or late. DATEDIF with "Y" is always right.

Practice task

  1. Open the HR sheet. F1 holds the report date 06-10-2026.
  2. Check D2 shows 25 for Ruhi.
  3. Change F1 to 15-07-2026. Ruhi's age stays 25 and the extra months become 0.
  4. Put F1 back and check the probation end date in I2 (01-12-2024).
  5. Add your own date of birth in a new row and find your age.

Learn it properly

Course lessons:

Related guides: NETWORKDAYS and WORKDAY in Excel (with Sunday-Only Weekends) · SUMIFS with Dates: Total Between Two Dates or for One Month

Frequently asked questions

Why can I not find DATEDIF in Excel?

It is an older function that Excel keeps for compatibility, so it does not show in the suggestions. Type it fully and it works.

How do I calculate age from date of birth?

=DATEDIF(B2,TODAY(),"Y") gives complete years today.

Why does my answer show a date like 25-01-1900?

The cell is formatted as a date. Change it to General or Number.

YEARFRAC or DATEDIF?

DATEDIF for complete years, months and days; YEARFRAC when you need a decimal, for example for interest.