How to Calculate Age from Date of Birth in Excel

Method 1: completed years with DATEDIF

=DATEDIF(A2,TODAY(),"Y") returns the number of complete years between the birth date and today. The "Y" unit excludes the unfinished year, so the result changes on the birthday rather than on January 1. Format the result cell as General or Number, not as a date.

For a report that must not change tomorrow, place the reporting date in E1 and use =DATEDIF(A2,$E$1,"Y"). Label E1 clearly as As of date. This makes payroll, eligibility, and historical reports reproducible; TODAY is appropriate only for a live dashboard where automatic change is intended. Total months are available with =DATEDIF(A2,$E$1,"M"), and an eligibility rule such as =IF(DATEDIF(A2,$E$1,"Y")>=18,"Adult","Minor") keeps the threshold visible in the formula. For the leftover months, =DATEDIF(A2,$E$1,"YM") returns the months after the last full year.

Method 2: decimal age with YEARFRAC

When an analysis needs an approximate age including part of the current year, use =YEARFRAC(A2,$E$1,1). Basis 1 uses actual days in actual years. Do not simply divide the day difference by 365, because leap years make that drift. Round only for display or according to a documented business rule.

Decimal age is not a substitute for completed age in policy decisions: 17.99 is still 17 completed years. State whether the requirement is completed years, decimal years, months, or days before choosing a formula.

Validate dates and exceptional cases

Excel stores recognized dates as serial numbers. A birth date imported as text can cause an error or an incorrect comparison; test with ISNUMBER and fix the import. Use DATEVALUE only when the text format is consistent and unambiguous, and prefer ISO-formatted imports over strings such as 03/04/05.

A future birth date makes DATEDIF return #NUM!. Flag it with =IF(A2>$E$1,"Check date",DATEDIF(A2,$E$1,"Y")). Error handling should communicate the data problem rather than silently convert every failure to zero. For a person born on February 29, ask the policy owner how a non-leap year is treated; DATEDIF counts the birthday as reached on March 1.

Common mistakes

Using =YEAR(TODAY())-YEAR(A2) overstates age before this year’s birthday. Using TODAY in an archived report makes past outputs change. Reversed arguments (end date first) give #NUM!, and DATEDIF is a compatibility function that Excel does not suggest while you type, although it works. Retain the as-of date beside exported results so the age can be interpreted later.