Calculate Age from Date of Birth using Excel

I can still remember how my father used to sit with paper and pen to calculate age from birthdate using some math trick. That manual approach is good but what if you need to calculate age for 1000 people? Using Excel we can calculate this amount of age from date of birth in seconds.

Methods to Calculate Age in Excel

Now there are various ways you can calculate age from date of birth (DOB) with excel. I will show you some of the popular ones. You can go with any of those for the calculation of age in excel.

Note: I am using term “date of birth” but you can utilize following methods to calculate age from any two dates. The idea is just to find difference between two dates.

Also Read: Top 12 movies with Artificial Intelligence

Method1: Using the DATEDIF Function

The DATEIF function is one of the popular functions in excel to calculate age from birthdate. To determine the age, use the following formula:

=DATEDIF(A3, TODAY(), "Y")

Here, A3 is the column for date of birth and TODAY() is for current date (or date of today. You can use any other date also). Here “Y” is to calculate age by year. You can replace it with “M” to calculate age by month.

Note: All function is tested on Microsoft Office Professional Plus 2019. Though all the functions should work in different versions of Excel.

Similarly, we can calculate age in years months, and days separately.

Method2: Using YEARFRAC Function

Another approach for age calculation YEARFRAC function in excel. This function calculates the fraction of a year or age between two dates.

=INT(YEARFRAC(B2, TODAY()))

In below image, you can see YEARFRAC function returns a decimal value representing the fraction of a year. We can use INT function to convert that fraction of year to a rounded age.

Method3: INT Function with DATEDIF

By combining the INT function with DATEDIF, we can also make accurate age calculations. This method is especially useful when you need to calculate the age in terms of years, months, and days in excel. Use the following formula:

=DATEDIF(A3, TODAY(), "Y") & " years, " & DATEDIF(A3, TODAY(), "YM") & " months, " & (DAY(TODAY()) - DAY(A3) + IF(OR(MONTH(A3) > MONTH(TODAY()), AND(MONTH(A3) = MONTH(TODAY()), DAY(A3) > DAY(TODAY()))), DAY(DATE(YEAR(TODAY()), MONTH(TODAY())+1, 0)), 0)) & " days"

This formula accurately calculates the number of Year month and days for a person by considering leap years and month boundaries. It also take care of the possibility of birth date being later in the current month than the current date.

Method4: Other Functions

You can also use some other popular built-in functions of excel to calculate age based on birthdate. You can use functions like YEAR, MONTH, and DAY. These functions is to extract the year, month, and day components from a given date, respectively.

With these functions, you can calculate your age as on a specific date. This is kind of a technique to calculate age manually but in excel. Use the following formula:

=IF(DATE(YEAR(TODAY()),MONTH(A3),DAY(A3)) > TODAY(), YEAR(TODAY()) - YEAR(A3) - 1, YEAR(TODAY()) - YEAR(A3))

This formula subtracts the birth year from the current year. If the birth month and day are greater than the current month and day, respectively, it subtracts an additional year to ensure accurate age calculation.

Conclusion

Calculation of age in Excel is a common task that often comes across in various fields. Excel provides multiple methods to achieve accurate age calculation. Throughout this blog post, we explored different techniques for calculating age based on a given date of birth.

So, go ahead and apply these methods to your Excel spreadsheets. And let me know if you want me to add anything else to this method list.