How to calculate age from ID number in Excel?
Supposing, you have a list of ID numbers which contain 13 digit numbers, and the first 6 numbers is the birth date. For example, the ID number 9808020181286 means the birth date is 1998/08/02. How could you get the age from ID number as following screenshot shown quickly in Excel?
Calculate age based on birthday date:
Kutools for Excel contains a powerful Formulas utility which collects some commonly used formulas can help you do some usual calculations quickly and easy, such as calculate age, add years, months, days to date, sum absolute values etc.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
To calculate age from ID number, the following formula can help you. Please do as this:
Enter this formula:
=DATEDIF(DATE(IF(LEFT(A2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(A2,2),"20"&LEFT(A2,2)),MID(A2,3,2),MID(A2,5,2)),TODAY(),"y") into a blank cell where you want to calculate the age, and then drag the fill handle down to the cells you want to apply this formula, and the ages have been calculated from the ID numbers at once, see screenshot:
1. In the above formula, A2 is the cell contains the ID number you want to calculate the age based on.
2. With the above formula, if the year is less that current year it will be considered as 20, if year is greater than current year will be considered as 19. For instance, if this year is 2016, the ID number 1209132310091’s birth date is 2012/09/13; the ID number 3902172309334’s birth date is 1939/02/17.