Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Virginia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    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()))

    then add another field
    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.
    Regards
    John



  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  6. #6
    New Lounger
    Join Date
    Nov 2001
    Location
    Virginia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age in Year and Month (A2K)

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

  7. #7
    New Lounger
    Join Date
    Nov 2001
    Location
    Virginia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    New Lounger
    Join Date
    Nov 2001
    Location
    Virginia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •