Ultimate Guide: 5 Formulas To Calculate Age In Excel Now

Calculating age in Excel can be a handy skill, especially when dealing with data that involves birthdates or expiration dates. In this comprehensive guide, we'll explore five powerful formulas that will make age calculation a breeze. Whether you're a data analyst, a researcher, or just someone who wants to organize personal information, these formulas will simplify your age-related calculations.
Formula 1: Basic Age Calculation

The most fundamental method to calculate age in Excel is by subtracting the birthdate from the current date. This formula is straightforward and perfect for a quick age calculation.
Formula: =TODAY() - BIRTHDATE
Here, TODAY()
function returns the current date, and BIRTHDATE
is the cell containing the birthdate.
Example: If the birthdate is in cell A2
, the formula would be =TODAY() - A2
. This will give you the age in days.
Formula 2: Calculating Age in Years

To get the age in years, we can use the YEARFRAC
function. This function calculates the fraction of a year between two dates and can be handy for age calculations.
Formula: =YEARFRAC(BIRTHDATE,TODAY(),1)
In this formula, BIRTHDATE
is the cell containing the birthdate, TODAY()
returns the current date, and 1
indicates that we want the result in years.
Example: If the birthdate is in cell A2
, the formula would be =YEARFRAC(A2,TODAY(),1)
. This will give you the age in years.
Formula 3: Age with a Twist - Calculating Age in Months

Sometimes, you might need to calculate age in months. Excel provides a function called DATEDIF
that can do this. However, it's important to note that DATEDIF
is a hidden function and may not be available in newer versions of Excel.
Formula: =DATEDIF(BIRTHDATE,TODAY(),"m")
In this formula, BIRTHDATE
is the cell containing the birthdate, TODAY()
returns the current date, and "m"
specifies that we want the result in months.
Example: If the birthdate is in cell A2
, the formula would be =DATEDIF(A2,TODAY(),"m")
. This will give you the age in months.
Formula 4: Handling Partial Years - Age in Years and Months

If you want to get the age in years and months, you can combine the YEARFRAC
and DATEDIF
functions.
Formula: =INT(YEARFRAC(BIRTHDATE,TODAY(),1)) & " years " & DATEDIF(BIRTHDATE,TODAY(),"m") & " months"
In this formula, INT
function is used to get the integer part of the age in years, and the rest of the formula calculates the months.
Example: If the birthdate is in cell A2
, the formula would be =INT(YEARFRAC(A2,TODAY(),1)) & " years " & DATEDIF(A2,TODAY(),"m") & " months"
. This will give you the age in years and months.
Formula 5: Calculating Age with Custom Date Formats

You can also format the age to display in a specific date format. This can be useful when you want to present the age in a more readable manner.
Formula: =TEXT(TODAY() - BIRTHDATE,"yyyy-mm-dd")
In this formula, TEXT
function is used to format the result. "yyyy-mm-dd"
is the custom date format you want to use. You can replace it with any other format as per your requirement.
Example: If the birthdate is in cell A2
, the formula would be =TEXT(TODAY() - A2,"yyyy-mm-dd")
. This will give you the age in the specified date format.
Table: Age Calculation Formulas

Formula | Description |
---|---|
=TODAY() - BIRTHDATE |
Basic age calculation in days |
=YEARFRAC(BIRTHDATE,TODAY(),1) |
Age calculation in years |
=DATEDIF(BIRTHDATE,TODAY(),"m") |
Age calculation in months (hidden function) |
=INT(YEARFRAC(BIRTHDATE,TODAY(),1)) & " years " & DATEDIF(BIRTHDATE,TODAY(),"m") & " months" |
Age calculation in years and months |
=TEXT(TODAY() - BIRTHDATE,"yyyy-mm-dd") |
Age calculation with custom date format |

Conclusion

Excel offers a range of powerful formulas to calculate age, from basic calculations to more complex ones. By using these formulas, you can easily determine the age of individuals or entities based on their birthdates. Whether you're working with personal data or conducting research, these age calculation formulas will streamline your data analysis and make your work more efficient.
Can I use these formulas for expiration dates as well?

+
Absolutely! These formulas can be adapted to calculate the time remaining until an expiration date by simply replacing the birthdate with the expiration date.
What if I want to calculate age in weeks or days only?

+
You can modify the formulas to suit your needs. For age in weeks, you can divide the result by 7, and for days, you can use the DAYS
function.
Is there a way to automatically update the age calculation as time passes?

+
Yes, you can use the NOW
function instead of TODAY
to ensure the age calculation updates every time the spreadsheet is opened.