Excel: How to Calculate Age in dd/mm/yyyy


You can use the following formulas to calculate age based on a birth date in dd/mm/yyyy format in Excel:

Formula 1: Calculate Age in Years (e.g. 32 years)

=DATEDIF(B2,NOW(),"y")

Formula 2: Calculate Age in Decimal Years (e.g. 32.597 years)

=YEARFRAC(B2,NOW())

Formula 3: Calculate Age in Years, Months, Days (e.g. 32 years, 7 months, 5 days)

=DATEDIF(B2,TODAY(),"y")&" years, "&DATEDIF(B2,TODAY(),"ym")&" months, "&DATEDIF(B2,TODAY(),"md")&" days"

Each formula assumes that the birth date is located in cell B2.

The following examples show how to use each formula in practice with the following list of birth dates in Excel:

Example 1: Calculate Age in Years

To convert the date of birth to age for cell B2, we can type the following formula into cell C2:

=DATEDIF(A2,TODAY(),"y")

We can then click and drag this formula down to each remaining cell in column C:

Column C displays the age for each birth date in column B in terms of years.

Example 2: Calculate Age in Decimal Years

To convert the date of birth to an age in decimal years for cell B2, we can type the following formula into cell C2:

=YEARFRAC(B2,TODAY())

We can then click and drag this formula down to each remaining cell in column C:

Column C displays the age for each birth date in column B in terms of decimal years.

Example 3: Calculate Age in Years, Months, Days

To calculate age in years, months and days for cell B2, we can type the following formula into cell C2:

=DATEDIF(B2,TODAY(),"y")&" years, "&DATEDIF(B2,TODAY(),"ym")&" months, "&DATEDIF(B2,TODAY(),"md")&" days"

We can then click and drag this formula down to each remaining cell in column C:

Excel calculate age from dd/mm/yyyy format

Column C displays the age for each birth date in column B in terms of years, months, and days.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Calculate the Number of Months Between Dates in Excel
How to Convert Date to Month and Year Format in Excel
How to Calculate Average by Month in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *