Excel VBA: Calculate Age from Date of Birth

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can calculate age from the date of birth using VBA in Excel.


VBA Code to Calculate Age from Date of Birth (Quick View)

Sub Calculate_Age()

'Inserting the Inputs
Birthday = CDate("6/13/1995")
Today = Date
Days_of_Months = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)

'Effect of the Leap Year
If Year(Today) Mod 4 = 0 Then
Days_of_Months(1) = 29
End If

'Calculating the Age
Age_Day = Day(Today) - Day(Birthday)
If Age_Day < 0 Then
Age_Month = Month(Today) - Month(Birthday) - 1
Age_Day = Age_Day + Days_of_Months(Month(Today) - 1)
Else
Age_Month = Month(Today) - Month(Birthday)
End If
If Age_Month < 0 Then
Age_Year = Year(Today) - Year(Birthday) - 1
Age_Month = Age_Month + 12
Else
Age_Year = Year(Today) - Year(Birthday)
End If
'Displaying the Age
MsgBox Str(Age_Year) + " Years, " + Str(Age_Month) + " Months, " + Str(Age_Day) + " Days."

End Sub

overview image


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


An Overview of the VBA Code to Calculate Age from Date of Birth in Excel (Step-by-Step Analysis)

So, without further delay, let’s go to our main discussion today. We’ll break down the VBA code step-by-step to learn how to calculate age from the date of birth using VBA.

⧪ Step 1: Inserting the Necessary Inputs

At the very outset of the code, we have to insert the necessary inputs into the code. The inputs include:

  • The Date of Birth (Put your date of birth)
  • Today’s Date (The Date function of VBA returns this)
  • An Array Containing the Number of Days of Each Month
'Inserting the Inputs
Birthday = CDate("6/13/1995")
Today = Date
Days_of_Months = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)

Inserting Inputs to Calculate Age from the Date of Birth in Excel

⧪ Step 2: Effect of the Leap Year

Next, we’ve to put the effect of the leap year. If the current year is a leap year, the total number of days of February will be 29 in place of 28.

'Effect of the Leap Year
If Year(Today) Mod 4 = 0 Then
    Days_of_Months(1) = 29
End If

Changing for Leap Year to Calculate Age from the Date of Birth in Excel

⧪ Step 3: Calculating the Age

This is the most important step. We’ll calculate the age from the birth date in this step. The calculation is a bit complex. I’m not showing it in detail.

'Calculating the Age
Age_Day = Day(Today) - Day(Birthday)
If Age_Day < 0 Then
Age_Month = Month(Today) - Month(Birthday) - 1
Age_Day = Age_Day + Days_of_Months(Month(Today) - 1)
Else
Age_Month = Month(Today) - Month(Birthday)
End If
If Age_Month < 0 Then
Age_Year = Year(Today) - Year(Birthday) - 1
Age_Month = Age_Month + 12
Else
Age_Year = Year(Today) - Year(Birthday)
End If
 calculating age from date of birth

⧪ Step 4: Displaying the Age

Finally, it’s time to display the age. You can change it according to your needs.

'Displaying the Age
MsgBox Str(Age_Year) + " Years, " + Str(Age_Month) + " Months, " + Str(Age_Day) + " Days."

displaying age

So the complete VBA code will be:

â§­ VBA Code:

Sub Calculate_Age()

'Inserting the Inputs
Birthday = CDate("6/13/1995")
Today = Date
Days_of_Months = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)

'Effect of the Leap Year
If Year(Today) Mod 4 = 0 Then
    Days_of_Months(1) = 29
End If

'Calculating the Age
Age_Day = Day(Today) - Day(Birthday)
If Age_Day < 0 Then
    Age_Month = Month(Today) - Month(Birthday) - 1
    Age_Day = Age_Day + Days_of_Months(Month(Today) - 1)
Else
    Age_Month = Month(Today) - Month(Birthday)
End If
If Age_Month < 0 Then
    Age_Year = Year(Today) - Year(Birthday) - 1
    Age_Month = Age_Month + 12
Else
    Age_Year = Year(Today) - Year(Birthday)
End If

'Displaying the Age
MsgBox Str(Age_Year) + " Years, " + Str(Age_Month) + " Months, " + Str(Age_Day) + " Days."

End Sub

final VBA code

Read More: Excel formula to Calculate Age on a Specific Date


Developing the Macro to Calculate Age from Date of Birth

We’ve seen the step-by-step analysis of the code to calculate age from the date of birth.

Now let’s see how to develop a Macro to run the code.

⧪ Step 1: Opening VBA Window

Press ALT + F11 on your keyboard to open the Visual Basic window.

⧪ Step 2: Inserting a New Module

Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.

Inserting Module to Calculate Age from the Date of Birth in Excel

⧪ Step 3: Putting the VBA Code

This is the most important step. Insert the given VBA code in the module.

final VBA code

⧪ Step 4: Running the Code

Click on the Run Sub / UserForm tool from the toolbar above.

The code will run displaying the age in a Message Box.

Read More: How to Calculate Age Between Two Dates in Excel (6 Useful Methods)


Conclusion

Therefore, this is the process to calculate age from the date of birth using VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

2 Comments
  1. This is buggy, don’t use it. For compare against date of January the month – 2 will access an invalid position from the array and throw errors.

    • Hello Excel Guru,
      Thanks for your comment. The article has been updated, so you can try the code now easily for January month also. And the previous code was not buggy obviously, it was used for another purpose and so it also gave results.
      Thanks
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo