Method 1: Simple Year Subtraction
The simplest approach is to subtract the birth year from the current year using the formula =YEAR(TODAY())-YEAR(A1) where A1 contains the date of birth. This gives you an approximate age in years. The limitation is that it does not account for whether the birthday has occurred yet in the current year, so it can be off by one year for part of the calendar.
Method 2: Using DATEDIF Function
The DATEDIF function is more precise. The formula =DATEDIF(A1,TODAY(),"Y") returns the number of complete years between the birth date in A1 and today. To get months as well use =DATEDIF(A1,TODAY(),"YM") which gives remaining months after complete years. For remaining days use =DATEDIF(A1,TODAY(),"MD"). Combining these three gives you a complete age string in years, months, and days.
Method 3: Using YEARFRAC
The YEARFRAC function returns the fraction of a year between two dates. =INT(YEARFRAC(A1,TODAY())) gives you the completed years of age. This method is popular in financial applications. It handles leap years cleanly and is more mathematically precise than the simple year subtraction method.
Calculating Age in Total Days
To get the total number of days someone has been alive, simply use =TODAY()-A1 where A1 is the date of birth formatted as a date. Excel stores dates as sequential numbers, so subtraction gives you the exact day count. Format the result cell as a Number rather than a Date to see the count clearly.
Common Excel Errors in Age Calculation
The most common error is entering dates as text rather than as Excel date values. If your birth date column contains text strings like 01/05/1990 rather than properly formatted date values, the formulas will return errors. Use the DATEVALUE function to convert text dates to proper Excel dates before applying age formulas.
When to Use an Online Tool Instead
For quick one-off calculations, an online age calculator is faster and more convenient than setting up Excel formulas. Our free Age Calculator at GlobalAIMinds gives you a complete breakdown in seconds with no formula setup required. For bulk HR or database work with hundreds of rows, Excel formulas are the better long-term solution.