# Excel VBA: Calculate Age from Date of Birth

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) - 2)
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`````` ## 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)`````` ⧪ 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`````` ⧪ 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) - 2)
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`````` ⧪ 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."`````` 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) - 2)
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`````` ## 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. ⧪ Step 3: Putting the VBA Code

This is the most important step. Insert the given VBA code in the module. ⧪ 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. ## 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

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 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.

We will be happy to hear your thoughts 