Calculation of age from date of birth in Excel

The following article will guide you how to calculate the age from date of birth in Excel so you can calculate the exact number of standard age in years, months and days.

In the calculation of age based on date of birth, the following cases occur:

- Case 1 : Calculate the normal age by subtracting the year of birth from the current year

- Case 2: Calculate the age with conditions 365 days to be counted as 1 year.

- Case 3: Calculate the full age of the number of years, months and days left when less than 1 year.

The following article guides you how to calculate age in the three above cases:

1. Case 1: Calculate the usual age by subtracting the year of birth from the current year

In this case, the age is calculated by subtracting the year of birth from the current number of years, even though the number of months and days is not sufficient.

Step 1: At the cell to calculate the age enter the formula: = YEAR (TODAY () - YEAR (D6))

Calculation of age from date of birth in Excel Picture 1

Step 2: Press Enter to get the results:

Calculation of age from date of birth in Excel Picture 2

Step 3: Copy the formula for the remaining values:

Calculation of age from date of birth in Excel Picture 3

2. Case 2: Calculating the age with the condition of 365 days to be counted as 1 year.

In calculating the number of retirement years of insurance to reach the retirement age it is necessary to calculate the age with a condition of full 365 days to be rounded up to 1 year of age:

Step 1: In the cell to calculate enter the formula : = DATEDIF (D6, TODAY (), "Y")

Calculation of age from date of birth in Excel Picture 4

Step 2: Press Enter to get the results:

Calculation of age from date of birth in Excel Picture 5

Step 3: Copy the formula for the remaining values. Here we see 3 people born in 1964 but with different ages. The current date is January 3, 2018 (d / m / y) so only people born on March 1, 1964 will be 54 years old. The second person born on May 1, 1964 was missing 2 days to reach 54 years of age.

Calculation of age from date of birth in Excel Picture 6

3. Case 3: Calculating age for the number of years, months and days left when less than 1 year.

With the calculation of the full number of years with the number of months and days of excess is usually calculated in the case of retirement review and salary increase .

Step 1: In the cell to calculate enter the formula: = DATEDIF (D6, TODAY (), "y") & "age" & DATEDIF (D6, TODAY (), "ym") & "month" & DATEDIF (D6, TODAY ( ), "md") & "date"

Calculation of age from date of birth in Excel Picture 7

Step 2: Press Enter to get the results:

Calculation of age from date of birth in Excel Picture 8

Step 3: Copy the formula for the remaining values:

Calculation of age from date of birth in Excel Picture 9

This calculation explains why 3 people have the same year of birth but the time of retirement varies. Because the number of days and months is less than 1 year old, their retirement time varies. For example, the second person is missing 54 days old and not yet retired at the time of review. The 3rd person is missing 4 months and 8 days old enough to age.

Above is a detailed guide on how to calculate age from date of birth in Excel. Depending on the calculation and purpose you use the calculation accordingly.

Good luck!