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 from ID number with formula
Calculate age from ID number with formula
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:
Notes:
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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!