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!

Micah Soto

Update 19 May 2020

You should read it

May be interested

Instructions on how to fix reverse dates in Excel
when we install the english language on microsoft excel, you will have an english date error, the previous month and then the date and year. so how can i return to the correct format of day, month and year in excel?
Common calculation functions in Excel
excel is a spreadsheet that supports functions that help you in the process of calculating and processing data, if you know the calculation functions, how to use them, your work will be processed more quickly.
How to fix date format errors in Excel
you are new to excel and there are many strange problems. you are reversed the date format in excel. so how to fix this.
Change the slash to a dot in Excel format
by default, the excel date format often uses slashes, such as march 23, 2016 .... however, if your worksheet has multiple dates lists, you can use dots. in date format to make the worksheet easier to see, no glitches.
DATE function - The date function in Excel
the date () function returns a date that is formatted corresponding to the date entered as based on the date index.
Reveal the secret for importing and converting date formats in Excel
each type of date format you feel very picky and wordy. you find it annoying to have to enter that format.
Date functions in Excel, DAY, WEEKDAY, MONTH
do you want to automatically update the current date, separate years or calculate the distance between two time points? the article below, free download will guide you how to do this professionally with date functions in excel.
How to automatically update the Excel file editing time
when inserting and editing time excel file, you will control the latest time to edit the file if shared with many people.
Scientific proof: Month of birth has an impact on human health and personality
do you know how your birth month affects your health and personality? let's find out in this article!
How to convert a sequence of numbers into dates in Excel
on excel, we can convert the sequence of numbers into dates without manually entering data.