# Thread: Age in Year and Month (A2K)

1. ## Age in Year and Month (A2K)

How to use DateDiff to calculate age and return age in year and month in Access

Looking for equivalent to/from XL (undocumented?):

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days"

This will return a string like

33 years, 9 months, 18 days

TIA.

2. ## Re: Age in Year and Month (A2K)

Use the "m" of the DateDiff function, I think this gives the number of months between 2 dates. You just need to convert the months to Years and Months.
HTH
Pat

3. ## Re: Age in Year and Month (A2K)

Using DateDiff with "m" gives a number of months that is too high by 1 if you haven't yet reached your birthday date in the current month.
Try this instead in a query
months: IIf(Day(Date())<Day([dob]),DateDiff("m",[dob],Date())-1,DateDiff("m",[dob],Date()))

days: DateDiff("d",DateSerial(Year([dob])+[months]12,Month([DOB])+[months] Mod 12,Day([dob])),Date())
then this one
age: [months]12 & " Years " & [months] Mod 12 & " months " & [days] & " days"
This will give expressions like 5 year 3 months 4 days.

For my test data it has given the correct results.

4. ## Re: Age in Year and Month (A2K)

If you want to use a function for this purpose, here is an example:
<pre>Public Function CalculateAge(StartDate As Date, _
EndDate As Date) As String

'Normally EndDate will = Now()
Dim intYears As Integer
Dim intMonths As Integer
Dim intDays As Integer
Dim intStartMonth As Integer

intYears = Year(EndDate) - Year(StartDate)
intMonths = Month(EndDate) - Month(StartDate)
intDays = Day(EndDate) - Day(StartDate)
intStartMonth = Month(StartDate)

If intDays < 0 Then
Select Case intStartMonth
Case 1, 3, 5, 7, 8, 10, 12 'Jan, Mar, May, Jul, Aug, Oct, Dec
intDays = intDays + 31
Case 4, 6, 9, 11 'Apr, Jun, Sep, Nov
intDays = intDays + 30
Case 2 'Feb
If Not IsLeapYear(Year(StartDate)) Then
intDays = intDays + 28 'Not Leap year
Else
intDays = intDays + 29 'Leap year
End If
End Select
intMonths = intMonths - 1
End If

If intMonths < 0 Then
intYears = intYears - 1
intMonths = intMonths + 12
End If

CalculateAge = intYears & " Years, " & _
intMonths & " Months, " & intDays & " Days"

End Function</pre>

To keep logic clearer, IsLeapYear is a separate function that determines if year in question is a leap year:
<pre>Public Function IsLeapYear(intYear As Integer) As Boolean

'Leap year every 4 years
'Exception: every 100 years unless divisible by 400
'Ex: 1800, 1900 = not leap year, 2000 = leap year

If intYear Mod 4 <> 0 Then 'Not leap year
IsLeapYear = False
Else
If intYear Mod 100 = 0 Then
If intYear Mod 400 = 0 Then
IsLeapYear = True
Else
IsLeapYear = False
End If
Else
IsLeapYear = True
End If
End If

End Function</pre>

The function has start and end date arguments in case you want to calculate age or elapsed period using date other than current date. I prefer using user-defined functions for this type of calculation than trying to persuade DateDiff to provide desired results.

HTH

5. ## Re: Age in Year and Month (A2K)

This user defined function should give you the results you are after.
<pre>Function fGetAgeYMD(DOB As Variant) As String
Dim intYears As Integer, intMonths As Integer, intDays As Integer
Dim strTmp As String

If Not IsDate(DOB) Then Exit Function

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

If intDays < 0 Then
intMonths = intMonths - 1
intDays = DateDiff("d", DateAdd("m", -1, Date) - intDays, Date)
End If

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

If intYears = 1 Then
strTmp = intYears & " Year "
Else
strTmp = intYears & " Years "
End If
If intMonths = 1 Then
strTmp = strTmp & intMonths & " Month "
Else
strTmp = strTmp & intMonths & " Months "
End If
If intDays = 1 Then
strTmp = strTmp & intDays & " Day "
Else
strTmp = strTmp & intDays & " Days "
End If

fGetAgeYMD = strTmp

End Function</pre>

HTH
RDH

6. ## Re: Age in Year and Month (A2K)

Thanks for the great instruct. Now getting to know my date Serial function. thnxagin

7. ## Re: Age in Year and Month (A2K)

Thanks a ton for the 411. Still not good with calling functions; somehow I manage to not get it; but this should help me cut my teeth. thnxagin

8. ## Re: Age in Year and Month (A2K)

ditto.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•