Forum Discussion
Automatic Age Calculation in Word Document
It is pretty simple with a macro:
Sub TestDetailed()
'MsgBox fcnCalcAge(InputBox("Enter birth date in ""MM/dd/yyyy"" formmat", "Birth Date"), Now)
MsgBox fcnCalcAge("12/31/1958 4:34", Now)
End Sub
Function fcnCalcAge(oDateBD As Date, oDateNow As Date, Optional ShowAll As Boolean = False, Optional Grammar As Boolean = True)
Dim lngYear As Long, lngMonth As Long, lngDay As Long
Dim oDateLDPM As Date, oDateLDIM As Date, oAnchorDate As Date
'Restrict to date only (i.e., no 12/31/1958 3:15 p.m.)
oDateBD = Int(oDateBD)
oDateNow = Int(oDateNow)
'Limit to to persons already borned.
If oDateBD > oDateNow Then
fcnCalcAge = "Come back after your birth date."
Exit Function
End If
'Calculate complete years passed.
If Year(oDateNow) > Year(oDateBD) Then
'A different calendar year. Has one or more complete years passed?
If Month(oDateNow) = Month(oDateBD) Then
'Same month in subsequent year. Check day.
If Day(oDateNow) >= Day(oDateBD) Then
'Complete year passed
lngYear = DateDiff("yyyy", oDateBD, oDateNow)
Else
'e.g, birthdate 6/14/2000, date now 6/13/2015 returns fourteen years.
lngYear = DateDiff("yyyy", oDateBD, oDateNow) - 1
End If
ElseIf Month(oDateNow) > Month(oDateBD) Then
'Complete year passed.
lngYear = DateDiff("yyyy", oDateBD, oDateNow)
Else
'e.g, birthdate 7/13/2000, date now 6/13/2015 returns fourteen years.
lngYear = DateDiff("yyyy", oDateBD, oDateNow) - 1
End If
Else
'Obviously no complete year passed.
lngYear = 0
End If
'Calculate full months passed from last full year.
lngMonth = (DateDiff("m", DateSerial(Year(oDateBD), Month(oDateBD), 1), _
DateSerial(Year(oDateNow), Month(oDateNow), 1)) + IIf(Day(oDateNow) >= Day(oDateBD), 0, -1)) Mod 12
'Calculate number of days passed from last full month.
If Day(oDateNow) >= Day(oDateBD) Then
lngDay = Day(oDateNow) - Day(oDateBD)
Else
'Calculate for end of month.
'Get date on last day of previous month.
oDateLDPM = DateSerial(Year(oDateNow), Month(oDateNow), 0)
'Get date on last day of index month.
oDateLDIM = DateSerial(Year(oDateNow), Month(oDateNow) + 1, 0)
oAnchorDate = DateSerial(Year(oDateNow), Month(oDateNow) - 1, Day(oDateBD))
If oDateLDIM = oDateNow Then
If lngMonth = 11 Then
'Reset month and add a year.
lngMonth = 0
lngYear = lngYear + 1
Else
lngMonth = lngMonth + 1
End If
Else
lngDay = DateDiff("d", IIf(oAnchorDate > oDateLDPM, oDateLDPM, oAnchorDate), oDateNow)
End If
End If
If lngYear >= 1 Then
fcnCalcAge = lngYear & IIf(lngYear = 1, " year, ", " years, ") & lngMonth & IIf(lngMonth = 1, " month, ", _
" months, ") & lngDay & IIf(lngDay = 1, " day", " days")
Else
If lngMonth >= 1 Then
fcnCalcAge = lngMonth & IIf(lngMonth = 1, " month, ", " months, ") & lngDay & IIf(lngDay = 1, " day", " days")
Else
fcnCalcAge = lngDay & IIf(lngDay = 1, " day", " days")
End If
End If
lbl_Exit:
Exit Function
End Function