Option Compare Database
Option Explicit
Public Function Age(Date1 As Variant, Optional Date2 As Variant = 0) As String
'https://www.access-programmers.co.uk/forums/threads/date-difference-in-years-months-days.121140/#post-1674999
'Provided by @arnelgp
'Fixes by @TheDBGuy
'Date1 = 8/1/97 and Date2 = 8/1/97 then Age(Date1, Date2) results in "1 year"
'Date1 = no date and Date2 = 1/1/92 then Age(Date1, Date2) results in "Unknown"
'Date1 = 9/1/18 and Date2 = no date then Age(Date1, Date2) results in "1 year 6 months and 7 days"
'Date1 = no date and Date2 = no date then Age(Date1, Date2) results in "Unknown"
Dim Year1 As Integer
Dim Month_1 As Integer
Dim Day1 As Integer
Dim temp As Date
Dim sAge As String
'the result will be Unknown if Date1 is empty or is not in a date format
Age = "Unknown"
If Trim(Date1 & "") = "" Then Exit Function
If IsDate(Date1) = False Then Exit Function
'the result will be empty if .....
Age = ""
'if Date2 is null/blank then make the value 0, then if 0 = 0 then make date2 = to today's date.
If Nz(Date2, 0) = 0 Then Date2 = Date
temp = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Year1 = Year(Date2) - Year(Date1) + (temp > Date2)
Month_1 = Month(Date2) - Month(Date1) - (12 * (temp > Date2))
'Day in date2 minus date1, gives the # of days
Day1 = Day(Date2) - Day(Date1)
'if day1 is less than 0 then
If Day1 < 0 Then
Month_1 = Month_1 - 1
Day1 = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + Day1 + 1
End If
'if Year1 is greater than 0 then
If Year1 > 0 Then
Age = Year1
'if the year # is greater than 1 use plural
If Year1 > 1 Then
Age = Age & " years "
'Otherwise use singluar
Else
Age = Age & " year "
End If
'if month_1 is greater than 0 and Day1 is equal to 0 then use below which will just join the year and month
If Month_1 > 0 And Day1 = 0 Then
Age = Age & " and "
End If
End If
'if month_1 is greater than 0 and based on the above the days are more than 0 then
If Month_1 > 0 Then
Age = Age & Month_1
'if the month # is greater than 1 use plural
If Month_1 > 1 Then
Age = Age & " months "
'Otherwise use singluar
Else
Age = Age & " month "
End If
End If
If Day1 > 0 Then
Age = Age & " and " & Day1
'if the day is greater than 1 use the plural
If Day1 > 1 Then
Age = Age & " days"
'Otherwise use the singluar
Else
Age = Age & " day"
End If
End If
sAge = Trim(Replace(Age, " ", " "))
If InStr(sAge, "and") = 1 Then _
sAge = Trim(Replace(sAge, "and", ""))
Age = sAge
End Function