How to Calculate Age in Excel from ID Number (4 Quick Methods)

Let’s consider the following dataset (in B4:C14) with people’s Names and their ID Numbers. The first 6 digits in ID Numbers represent the date of birth in the format yy-mm-dd. Excel doesn’t offer any built-in functions to calculate age from a number. However, we can combine a few functions to convert the date of birth to age.

Method 1 – Calculate the Age from the ID (Two Versions)

Case 1.1 – Calculate the Age from the ID with the MID Function

Steps:

• Add a Date of Birth column and insert this formula in the first cell:

Here, the C5 cell represents the ID Number (text argument), and the next two numbers represent the start_num and num_chars arguments respectively. The MID function extracts the first 6 digits from the ID Number text.

• Insert a Current Date column and use the TODAY function, which returns the present date:

• Calculate the Age in years by calculating the difference between the two dates and dividing by 365 days (copy the formula below). The INT function rounds the Age to the nearest whole number.

Case 1.2 – Using the DATE Function to Extract the Age from the ID 

Steps:

• Add a Date of Birth column and insert this formula in the first cell:

Here, the C5 cell represents the ID Number (text argument).

• Add a Current Date column and apply the TODAY function which returns the present date:

• Calculate the Age in years by calculating the difference between the two dates and dividing by 365 days. Use the following formula:

Read More: Metabolic Age Calculator in Excel

Method 2 – Compute the Age from the ID Number Directly

Case 2.1 – Calculate the Age Using DATEDIF and IF Functions 

• Copy the following formula into the first result cell (D5), press Enter, and AutoFill for the other results.

Formula Breakdown

• The MID function obtains the first 6 numbers from the ID Number in the C5 cell.
• The IF function checks whether the year is less than or greater than the current year.
• The DATEDIF function subtracts the birth year from the present year and returns the Age.

Case 2.2 – Calculate the Age with DATEDIF and DATE Functions 

Use this simplified formula:

In this expression, the C5 cell represents the ID Number (text argument), while, the two following numbers refer to the start_num and num_chars arguments respectively.

Formula Breakdown

• Initially, the DATE function takes in 3 arguments (year, month, day) which are provided by the MID function.
• Finally, the DATEDIF function returns the Age.

Method 3 – Convert an ID Number to Age with Data Tools

Steps

• Add a column (D5:D14) titled Date of Birth and copy and paste all ID Numbers in that column.
• Select all the cells in the column and click the Text to Columns button in the Data ribbon.

• A Convert Text to Columns Wizard appears.
• Select Fixed width since all ID Numbers have the same number of digits.
• Press Next.

• Click just after the 6th digit from the left side, as depicted in the image below.
• Press Next.

• Select the Date format from the drop-down and press Finish to close the dialog box.

• Add a column named Current Date (E5:E14) and enter the TODAY function to get the present date:

• Make another column for Age (F5:F14) and compute the difference between the two dates by copying the following function and auto-filling for other cells in the column.

Read More: How to Calculate Retirement Age in Excel

Method 4 – Using VBA Code

• Add a Date of Birth column and insert this formula:

• Go to the Developer tab and select Visual Basic.

• Insert a Module where you’ll paste the VBA code.

• Copy the code below and paste it into the window:

Public Function calculate_age(birth_date As Date, Optional result_index As Integer = 0) As Variant
    today_day = Day(Date)
    today_month = Month(Date)
    today_year = Year(Date)
    birth_date_day = Day(birth_date)
    birth_date_month = Month(birth_date)
    birth_date_year = Year(birth_date)
    If birth_date_month < today_month Then
        age = today_year - birth_date_year
    ElseIf birth_date_month = today_month Then
        If birth_date_day <= today_day Then
            age = today_year - birth_date_year
        Else
            age = today_year - birth_date_year - 1
        End If
    Else
        age = today_year - birth_date_year - 1
    End If
    Dim output_array(25) As Variant
    output_array(0) = age
    output_array(1) = Date
    output_array(2) = today_day
    output_array(3) = today_month
    output_array(4) = today_year
    output_array(5) = birth_date_day
    output_array(6) = birth_date_month
    output_array(7) = birth_date_year
    calculate_age = output_array(result_index)
End Function

• Close the VBA window and return to your worksheet.
• Type an Equal sign followed by the calculate_age function and enter a cell reference (D5) containing the date of birth to get the age.

Download Practice Workbook

Related Articles

• How to Calculate Average Age in Excel
• How to Group Age Range in Excel with VLOOKUP

<< Go Back to Calculate Age | Date-Time in Excel | Learn Excel