How to Calculate Age in Excel with DATEDIF (Years, Months, Days)

The age formula in Excel

=DATEDIF(C3,C4,"Y")

• C3: the date of birth (the start date).
• C4: the date you want the age on. Use =TODAY() for today, or type a date for a specific date such as a month end.
• “Y”: the unit, completed years. Other units: "M" months, "D" days, "YM" months after the last birthday, "MD" days after the last full month.

Step 1: Age in years with DATEDIF

Enter the date of birth in C3 and the as-of date in C4, then in C7:

=DATEDIF(C3,C4,"Y")

Someone born on Mar 18, 1988 is 38 on Oct 31, 2026. DATEDIF counts completed years, so they stay 38 until Mar 18, 2027. A simple =(C4-C3)/365 looks close but drifts with leap years and can show the new age a day early or late.

If the result looks wrong, check that both dates are real dates, not text. A date typed as text gives #VALUE!; see how to standardize mixed date formats.

Step 2: Age in years, months and days

Add the months after the last birthday and the days after the last full month:

=DATEDIF(C3,C4,"YM")

=C4-EDATE(C3,DATEDIF(C3,C4,"M"))

The first gives 7 months. The second counts total months, moves the birth date forward by that many months with EDATE, and subtracts: 13 days. You can also use DATEDIF(C3,C4,"MD"), which gives 13 here, but Microsoft warns that “MD” can return wrong results for some dates, so the EDATE version is the safer choice. Join the parts into one line of text:

=C7&" years, "&C8&" months, "&C10&" days"

The text result cannot be used in calculations; keep the numbers in their own cells. To format dates inside text, use TEXT, as in month labels with the TEXT function.

Step 3: Total months, total days and YEARFRAC

Subtracting two dates gives days, because Excel stores dates as day numbers. YEARFRAC returns the fraction of years between two dates; basis 1 uses the actual days in each year. INT drops the decimals, so =INT(YEARFRAC(C3,C4,1)) is an alternative age formula that shows up in autocomplete. The workbook checks it against DATEDIF in C16: Ties.

Step 4: Avoid #NUM! and blank-date surprises

Two things go wrong in real lists. If the start date is after the end date (a typo, or a future date), DATEDIF returns #NUM! in Excel. Check the order first:

=IF(C3>C4,"Check dates",DATEDIF(C3,C4,"Y"))

And if the date of birth is blank, Excel treats it as 0, a date in 1900, so the age shows as 126 instead of an error. Return a blank instead:

=IF(C3="","",DATEDIF(C3,C4,"Y"))

For other errors, wrap the formula in IFERROR, but only after you know why the error happens; see IFERROR in Excel.

Step 5: Ages and years of service for a team

On the Employees sheet, the as-of date is in C2 and each row has a date of birth and a hire date. The same formulas work down the list, with the as-of date locked by $:

=DATEDIF(B5,$C$2,"Y")

=E5&" yrs "&DATEDIF(C5,$C$2,"YM")&" mos"

The first is the age; with the hire date in C5 instead of B5, the same formula gives completed years of service (E5). The second shows the service as years and months, which is how service awards, probation periods and leave entitlements are usually measured.

Service-based leave? Combine years of service with an employee leave tracker. For working days between two dates rather than calendar time, use NETWORKDAYS with holidays.

Step 6: Next birthday and age bands

The next birthday is the date of birth plus (age + 1) years. EDATE adds months, so multiply by 12. If the birthday is today, show today:

=IF(EDATE(B5,12*D5)=$C$2,$C$2,EDATE(B5,12*(D5+1)))

Days to birthday is the next birthday minus the as-of date. Ana Reyes turns 35 on Nov 4, 2026, in 4 days; David Cruz’s birthday is the as-of date itself, so it shows 0. For Carla Mendoza, born on Feb 29, 2000, EDATE returns Feb 28, 2027, the last day of February in a non-leap year.

Count people in each age band with COUNTIFS, using the band limits in columns B and C:

=COUNTIFS($D$5:$D$12,">="&B15,$D$5:$D$12,"<="&C15)

The bands add up to 8, the headcount (check: Ties). For more on counting with two conditions on one column, see COUNTIFS with multiple criteria.

Common age formula mistakes

The last one surprises many people: when you type a formula next to a date column, Excel may copy the date format into the result. The age is right; only the format is wrong.

Frequently asked questions

Final checklist

• Both dates are real dates, not text.
• The date of birth is the first argument and the as-of date the second.
• The as-of date is fixed for a report, or =TODAY() for a live list.
• Days use the EDATE version instead of “MD”.
• Blank and reversed dates are handled with IF.
• The as-of date is locked with $ before you copy the formula down.