How to Calculate Age Using Excel

Have you ever wondered how you could calculate the age of a number of people in a short period of time? Well, excel simplifies this task for you. Since calculating age goes hand in hand with dates and numbers in excel, it is easier to calculate age when you follow a few steps. It is important to know the date of birth of the person when calculating one's age. The following steps  guide in calculating age in excel:

1. Create a Name column

It isn't compulsory to have the title 'Name' but bear in mind that this column will help identify each person you are calculating the birthday for

 

2. Create a Birthday column.

This column can contain every birthday as a separate line. It isn't a must you use this for birthdays only you can choose any starting point such we purchased date

 

3. Enter the birthdays using a common format.

The format could be either MM/DD/YYYY normally for US citizens or DD/MM/YYYY for non-US residents.

  • If the data is being auto-updated as something else, highlight the cells and click a drop-down menu in the Numbers section of the Home tab then select a short date from the list of options

 

4. Create an age column

This will display the age for each entry after you enter the formula

 

5. Select the primary empty cell in the Age column. This is where you will be entering the formula to calculate birthdays

 

6. Enter the formula for finding the age in years

=DATED IF (B2,TODAY ( ), "Y" )

Whereby:

=DATED IF ( ) is a function that calculates the difference between the two dates

( B2,TODAY ( ), "Y" ) tells DATED IF to calculate the distinction between the date in cell B2 (the initial birthday listed ) and the current date ( nowadays( ) )

To see age in days or months use "D" or "M" instead.

7. Click and drag the square within the bottom right corner of the cell down. This may apply an equivalent formula to every line, adjusting it consequently so that the proper birthday is calculated.

8. Modify the formula to calculate exact age in years , months and days .The advanced formula is

=DATED IF ( B2, TODAY ( ), "Y" )&years, "&DATED IF (B2,TODAY ( ), "Y") & "months, "& DATED IF (B2,TODAY ( ), "MD" ) & "Days"

Note that the DATED IF formula does not work for dates before 01/01/1900

Alternatively, EDUCBA.com gives a simplified formula for calculating age.

(B1-B2)/365.25

Whereby:

B1= current date

B2= date of birth

365.25= number of days in a year

 

To round off the decimal place in 365.25 use the ROUND DOWN function formula

=ROUNDDOWN ( Number, num-digit)

The number is used to round down the number and the num-digit is used to round down the number to many decimal places. Then apply the formula

=ROUNDDOWN ((B1-B2)/365.25,0)

 

%d bloggers like this: