How to Calculate Age in Excel?

Excel doesn’t have any specific function to calculate age. However, we can still calculate age with the help of certain functions present in Excel. We can calculate the age of any person incorporating his birth date and the current date in certain excel functions.

In this section, we will consider different methods or formulas to calculate age, we will also discuss their consequences. So, let us start with the steps for calculating age in excel.

Steps to Calculate Age in Excel 2016:

For the examples below the date of birth is in cell B2 and the current date is in cell B3.

Formula 1:

=(YEAR(NOW()) – YEAR (B2))

In the image below, you can observe that the result of the formula above is 30. So, the age of the person with the birth date 24-02-1990 is 30.

The YEAR() function in Excel returns the year of the date, fed in the function. The NOW() function returns the current date and time. You can also use TODAY() function in place of NOW() as TODAY() returns the current date. So finally, both the YEAR() functions in the formula return the year of the date fed to them which is then subtracted to get the result.

Formula 2:

=YEAR(TODAY())-1990

In the image below, you can see that the result of the formula above is 30. Here, the TODAY function has return today’s date i.e. the current date. This current date is fed to YEAR() function which in turn returns the year of current date i.e. 2020 (for this example) which is then subtracted from year of the birth 1990 (for this example).

Formula 3:

=YEARFRAC(B2, B3, 1)

The result for the formula above for this example is 30.0636757. The YEARFRAC() function returns the number of years passed between the start date and end date in a fraction.

Formula 4:

=(B2-B3)/365.25

The formula above is very simple, we are just subtracting two cells with birth date and current date, this would subtract the two serial numbers corresponding to birth date and current date by which Excel recognizes that particular date.

It would return the number of days between the birth date and current date which is then divided by 365.25 to get the number of years passed between birth date and current date.

Formula 5:

=DATEDIF(Start_date, End_date, “Y” )

DATEDIF function returns the number of years passed between the start_date and end_date if the last argument is “Y”.

DATEDIF function returns the number of months passed between the start_date and end_date if the last argument is “M”. To get months passed after the complete number of years passed between the start_date and end_date, the argument should be “YM”.

DATEDIF function returns the number of days passed between the start_date and end_date if the last argument is “D”. To get days passed after the complete number of months passed between the start_date and end_date, the argument should be “MD”.

In the image below, you can observe we have concatenated the same function three times using ‘&’ to get the number of years, months & days between birth date and current date.

Formula 6:

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

In the examples, above we got the result in a fraction. But in general, we talk of age as a round figure. So, in the formula above we have used ROUNDDOWN() function along with YEARFRAC() to round off the value returned by YEARFRAC().

So, this is how you can calculate age in excel. Friends this is not all, you can calculate age with many other functions of Excel. Explore more to get other methods too. Hope the content is useful. Keep reading.