Calculate Age in Excel via Date of Birth (Years, Months, Days)

If you want to calculate the age of all your team members using their birthday date then use this DATEDIF formula that we have used below to calculate age in excel in terms of years, months, and days from today’s date or specific date.

1. Calculate Age in Excel (DATEDIF)

To calculate the age, you will not find any function as such but there are various ways that let you convert sate into the birth date.

Calculate Age in Excel

Suggested Read: Excel Square Root

The DATEDIf function will return the difference between the two dates in different time such as years, months, and days.

  • Y: It returns the total number of years that comes between the start and end dates.
  • M: It returns the total number of months between dates.
  • D: It returns the number of days between the two dates.
  • YM: It returns the total number of months.
  • MD: It returns the difference in the days while ignoring the months.
  • YD: It returns the difference between the days.

DATEDIF(date of birth, Today(), “y”)

The syntax of this

=DATEDIF(start_date, end_date, unit)

Let’s take an example; here you can take a data set. Apply the formula and then calculate the age.

=DATEDIF(B2, Today(),”y”)

Calculate Age in Excel in Years and Months and Days

To calculate the age in years, you need to apply this formula,

=DATEDIF(B2, TODAY(),”Y”)

To get values in months,

=DATEDIF(B2, TODAY(),”YM”)

To get values in days,

=DATEDIF(B2, TODAY(),”MD”)

Here you can check the combined formula,

=DATEDIF(B2, TODAY(),”Y”)&” Years “&DATEDIF(B1,TODAY(),”YM”)&” Months ”&DATEDIF(B2,TODAY(),”MD”)&” DAYS”

Where

  • DATEDIF(): Calculate the total number of days, months, and years between the two specified dates.
  • TODAY(): The function will give you the current date.
  • DATE(): Gives you the date that defines the year, month, and date value as an argument.
  • INT(): Returns the integer value.
  • YEARFRAC(): It calculates the total number of years that passed between the start date and end date.

How to Calculate Age Manually?

If you want to calculate the age manually then you can use this formula

=(TODAY()-B2)/365

how to calculate age manually

Suggested Read: Standard Deviation in Excel

But this formula will give you decimal values. So to round off the decimal values you need to use integers.

=INT((TODAY()-B2)/365)

How to calculate age

Calculate Age in Excel (YEARFRAC)

Another way to find the age of the candidate is YEARDRAC function. With this function, it will return the fraction of the year i.e the number of days comes between the two dates. The Syntax of YEARFRAC is

=YEARFRAC(start_date, end_date, [basis]) where

  • Start_date is the date of birth
  • End_date is Today() i.e. today’s date
  • Basis consists the division of the total number of days per month with the total number of days per year.

Using the above formula, you will get the value in decimals but to fix them you need to use a ROUNDDOWN function that gives the round off values. The final formula is

=ROUNDDOWN(YEARFRAC(B2, TODAY(), 1),0)

Calculate Age

Here in the screenshot, you can see the result of this formula.

Hence using these three functions you can calculate age in excel. Follow the step by step process and get the result. If you have any queries then you can mention your problem in the comment box listed below.

Leave a Reply

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