Calculation of Age By using VBA Macros

Functions Used:

Year Function
Month Function
Day Function
DateDiff Function
DateSerial Function

Created Custom function:

User can use the custom function in Macro enabled workbook or copy and paste the code into the workbook which consists of data

Created Workbook Addin:

User can download the addin and import into the workbook

Click on below mentioned image to watch the video:

Download The Workbook

Sub Calculation_Of_Age()
Dim R As Integer
Range("C2:E12").ClearContents
Application.Wait (Now + TimeValue("00:00:01"))
For R = 2 To Range("A" & Rows.Count).End(xlUp).Row
BY = Year(Cells(R, 1))
BM = Month(Cells(R, 1))
BD = Day(Cells(R, 1))

CY = Year(Cells(R, 2))
CM = Month(Cells(R, 2))
CD = Day(Cells(R, 2))
'===========Days difference==========
If CD > BD Then
D = CD - BD
End If

If CD = BD Then
D = 0
End If

If CD < BD Then
D = DateDiff("d", DateSerial(CY, CM - 1, BD), DateSerial(CY, CM, CD))
D = D - 1
CM = CM - 1
End If

'===========Months Difference==========

If CM > BM Then
M = CM - BM
End If

If CM = BM Then
M = 0
End If

If CM < BM Then
M = DateDiff("M", DateSerial(CY - 1, BM, BD), DateSerial(CY, CM, CD))
CY = CY - 1
End If

'===========Year Difference==========

Y = CY - BY

Cells(R, 3).Value = Y
Cells(R, 4).Value = M
Cells(R, 5).Value = D
Next
MsgBox "Calculated The Age"
End Sub

Code For Custom Function

Function Age(BirthDate, CurrentDate)
BY = Year(BirthDate)
BM = Month(BirthDate)
BD = Day(BirthDate)

CY = Year(CurrentDate)
CM = Month(CurrentDate)
CD = Day(CurrentDate)
'===========Days difference==========
If CD > BD Then
D = CD - BD
End If

If CD = BD Then
D = 0
End If

If CD < BD Then
D = DateDiff("d", DateSerial(CY, CM - 1, BD), DateSerial(CY, CM, CD))
D = D - 1
CM = CM - 1
End If

'===========Months Difference====

If CM > BM Then
M = CM - BM
End If

If CM = BM Then
M = 0
End If

If CM < BM Then
M = DateDiff("M", DateSerial(CY - 1, BM, BD), DateSerial(CY, CM, CD))
CY = CY - 1
End If

'===========Year Difference====
Y = CY - BY
Age = Y & " Years, " & M & " Months, " & D & " Days"
End Function