Calculate Age or Years of Service in Excel

To calculate age in Excel, use DATEDIF to count the complete years between a birth date and today, or any other end date. Years of service, or tenure, is the same calculation with a hire date. This guide shows the formula, why the two shortcuts most people try give wrong answers, and how to write tenure as years and months.

What is the formula to calculate age in Excel?#

Use DATEDIF with the unit "Y":

=DATEDIF(B2,TODAY(),"Y")

"Complete" is the key word. Someone born on July 15 doesn't turn a year older until July 15, and DATEDIF follows that rule. If the start date is after the end date, DATEDIF returns #NUM!, so check the order.

Excel doesn't suggest DATEDIF as you type, and it doesn't show its arguments. It still works in current versions on Windows and Mac; type the full name. The DATEDIF function guide covers every unit.

How do you calculate age on a fixed date?#

Put the date in a cell and point at it. Reports, audits and job tests usually need an "as of" date, because an answer based on TODAY() is different tomorrow:

=DATEDIF(B2,$F$1,"Y")

Here F1 holds =DATE(2025,6,30). The dollar signs keep every row pointing at F1 when you fill down. Building the date with DATE avoids regional confusion: 6/30/2025 is a valid US date, but a UK copy of Excel might read a typed date like that as text. The results:

Ben turns 40 exactly on the report date, and DATEDIF counts it. Ana and Eli have birthdays in July, so they haven't reached their next year yet.

For a live sheet, such as an HR list that should always be current, TODAY() is the right choice. The trade-offs are explained in TODAY vs NOW in Excel.

Why not subtract the years or divide by 365?#

Both shortcuts look right and give wrong answers for some people:

• Subtracting the years ignores the month and day. Anyone whose birthday is later in the year gets one year too many. Ana and Dana are both overcounted.
• Dividing by 365 ignores leap years. Over 24 years, the extra leap days add up, so Eli is pushed past his birthday a few days early. Dividing by 365.25 is closer, but it can still be a day out around birthdays.

DATEDIF compares the actual month and day, so it's right every time. If an interviewer asks why you didn't subtract the years, this table is your answer.

How to calculate years of service#

Tenure uses the same formula with the hire date. Add the "YM" unit for the months left over after the complete years:

=DATEDIF(C2,$F$1,"Y")&" yrs, "&DATEDIF(C2,$F$1,"YM")&" mos"

Use "M" for total complete months, which is handy for probation periods or "months active" counts. Ben's total is 70 months. The spaces inside " yrs, " and " mos" keep the text readable.

For leavers, use the leaving date as the end date instead of TODAY() or the report date. For more on the units, and on plain day counts, see how to count the days between two dates.

How do you get age with decimals?#

YEARFRAC returns the fraction of years between two dates:

=YEARFRAC(B2,$F$1,1)

For Ana, that's about 34.96. The 1 is the basis: it tells Excel to use the actual number of days in each month and year. Decimal ages are useful for averages, such as the average age of a team. For whole years, INT(YEARFRAC(...)) usually matches DATEDIF, but DATEDIF says what you mean more clearly.

What about leap-day birthdays and other edge cases?#

A few cases are worth knowing before a test:

• Born on February 29. In years without a February 29, DATEDIF counts the new year from March 1. Chen turns 25 on Mar 1, 2025, not Feb 28.
• Dates stored as text. If a birth date sits on the left of the cell, it's probably text, and DATEDIF returns #VALUE!. =ISNUMBER(B2) returns TRUE for a real date.
• Results shown as dates. If an age displays as something like 1/3/1900, the cell has a date format. Change it to General.
• Age bands. To group people into bands such as under 30, 30 to 44 and 45 or over, use the age in a nested IF or IFS.

To build dates from separate year, month and day columns first, see DATE, YEAR, MONTH and DAY in Excel. To check which day of the week an anniversary falls on, see WEEKDAY in Excel. Age and tenure questions are common in HR roles; see the Excel skills HR jobs ask for.

Practise it#

Try the age exercise on this page, then practise DATEDIF and tenure in a live spreadsheet, from simple day counts to a "7 yrs, 11 mos" label. When you're ready, the gym membership skill summary puts DATE, EDATE, DATEDIF and TEXT together on one sheet.