Calculate a person’s age from their birthdate in Excel VBA

This code takes into account years, months, and days, ensuring accurate results even considering whether the birthday has already passed this year or not.

Step 1: Access the VBA Editor

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the VBA editor, go to Insert > Module to create a new module.
3. Copy and paste the following code into the new module.

VBA Code: Calculate Age

Function CalculateAge(BirthDate As Date) As String
    ' This function calculates age based on the birthdate   
    Dim CurrentDate As Date
    Dim AgeYears As Integer
    Dim AgeMonths As Integer
    Dim AgeDays As Integer
    Dim BirthdayThisYear As Date  
    ' Get the current date
    CurrentDate = Date   
    ' Calculate the birthday for this year
    BirthdayThisYear = DateSerial(Year(CurrentDate), Month(BirthDate), Day(BirthDate))   
    ' If the birthday hasn't passed yet this year, subtract 1 from the age
    If CurrentDate < BirthdayThisYear Then
        AgeYears = Year(CurrentDate) - Year(BirthDate) - 1
    Else
        AgeYears = Year(CurrentDate) - Year(BirthDate)
    End If   
    ' Calculate the age in months
    If Month(CurrentDate) < Month(BirthDate) Or (Month(CurrentDate) = Month(BirthDate) And Day(CurrentDate) < Day(BirthDate)) Then
        AgeMonths = Month(CurrentDate) - Month(BirthDate) + 12
    Else
        AgeMonths = Month(CurrentDate) - Month(BirthDate)
    End If   
    ' Calculate the remaining days
    If Day(CurrentDate) < Day(BirthDate) Then
        ' If the current day is smaller than the birth day, calculate the remaining days of the previous month
        AgeDays = Day(CurrentDate) + (Day(DateSerial(Year(CurrentDate), Month(CurrentDate), 0)) - Day(BirthDate))
    Else
        AgeDays = Day(CurrentDate) - Day(BirthDate)
    End If  
    ' Return the age as a string in the format "x years, y months, z days"
    CalculateAge = AgeYears & " years, " & AgeMonths & " months, " & AgeDays & " days"
End Function

Explanation of the Code

1. Variable Declaration:
   • BirthDate: The person’s birthdate, passed as an argument to the function.
   • CurrentDate: The current date.
   • AgeYears, AgeMonths, AgeDays: These variables will hold the person’s age in years, months, and days, respectively.
   • BirthdayThisYear: The calculated birthday for the current year.
2. Age Calculation (Years):
   • If the birthday hasn’t passed this year yet, subtract 1 year from the calculated age.
3. Age Calculation (Months):
   • If the current month is earlier than the birth month or the current month is the same as the birth month but the birthday hasn’t occurred yet, adjust the month difference by adding 12 months.
4. Remaining Days Calculation:
   • If the current day is less than the birth day in the month, calculate how many days are left in the previous month.
5. Returning the Result:
   • The function returns the age as a text string in the format: « x years, y months, z days ».

Step 2: Using the Function in Excel

1. Go back to your Excel sheet.
2. Use the function like a normal Excel function. For example, in a cell, you would type:

=CalculateAge(A1)

Where A1 is the cell containing the birthdate. Make sure the cell contains a valid date format.

Example

If the birthdate is March 25, 1990, and today’s date is November 28, 2024, the function will return:

34 years, 8 months, 3 days

Notes:

• If you prefer to show the age in a different format, such as just in years, you can modify the code by removing or adjusting the portion that calculates months and days.
• This function accounts for all date-related nuances (including leap years) and will give an accurate result, even if the birthday is yet to come this year