DateDiff

[Yram]

I have used DateDiff in a query for a report to calculate a person's age in YEARS, but is it possible to get a calculation in YEARS/MONTHS?

 

[raskew]

This emulates the process followed when computing date difference manually.

EXAMPLE:
(year mo day)
Today's date: 2001 01 25
DOB: 1976 11 27

Step 1: If day(DOB) > day(Today), borrow 1 month from month(today) and 30 days to day(today)

Today's date: 2001 00 55
DOB: 1976 11 27

Step 2: If month(DOB) > month(Today), borrow 1 year from year(today) and 12 months to month(today)

Today's date: 2000 12 55
DOB: 1976 11 27

Step 3: Subtract each component of DOB from Today to give age in years, months, days
24 years 1 month 28 days


'****************************
Function AgeCount(varDOB As Variant, varDate As Variant) As String
'
' PURPOSE: Determines the difference between two dates.
'
' ARGUMENTS: (will accept either dates (e.g., #03/24/00#) or
' strings (e.g., "03/24/00")

' varDOB: The earlier of two dates.
' varDate: The later of two dates.
'
' RETURNS: A string as years.months.days, e.g., (17.6.21)
'
' NOTES: To test: Type '? agecount("03/04/83", "03/23/00")
' in the debug window. The function will
' return "17.0.19".

Dim dteDOB As Date, dteDate As Date, intoldyears As Integer
Dim intoldMonths As Integer, intoldDays As Integer
Dim intnuyears As Integer, intnumonths As Integer, intnudays As Integer
Dim intyears As Integer, intmonths As Integer, intdays As Integer

Dim agehold As String

If Not IsNull(varDOB) And Not IsNull(varDate) Then
dteDOB = DateValue(varDOB)
dteDate = DateValue(varDate)

intoldyears = Year(dteDOB)
intoldMonths = Month(dteDOB)
intoldDays = day(dteDOB)
intnuyears = Year(dteDate)
intnumonths = Month(dteDate)
intnudays = day(dteDate)

If intnudays < intoldDays Then
intnudays = intnudays + 30
intnumonths = intnumonths - 1
End If
If intnumonths < intoldMonths Then
intnumonths = intnumonths + 12
intnuyears = intnuyears - 1
End If


intyears = intnuyears - intoldyears
intmonths = intnumonths - intoldMonths
intdays = intnudays - intoldDays
agehold = LTrim(Str(intyears)) & "." & LTrim(Str(intmonths)) & "." & LTrim(Str(intdays))
AgeCount = agehold
End If

End Function

 

[R. Hicks]

I'm sure raskew's reponse will work, but maybe this is a little shorter way.

' Give Age in Years, Months
Function GetAgeYM(DOB As Variant) As String
Dim intYears As Integer, intMonths As Integer
Dim strTmp As String

If Not IsDate(DOB) Then Exit Function

intMonths = Int(DateDiff("m", DOB, Date))
intYears = Int(intMonths / 12)
intMonths = Int(intMonths Mod 12)

If intMonths < 0 Then
&nbsp intYears = intYears + intMonths
&nbsp intMonths = 12 + intMonths
End If

If intYears = 1 Then
&nbsp strTmp = intYears & " Year "
Else
&nbsp strTmp = intYears & " Years "
End If
If intMonths = 1 Then
&nbsp strTmp = strTmp & intMonths & " Month "
Else
&nbsp strTmp = strTmp & intMonths & " Months "
End If

GetAgeYM = strTmp

End Function

HTH
RDH