[AccessD] Date Difference As Completed Years-Months-Days

Hi A.D.

Thanks! 
Your approach is slightly different than mine and it reveals the issues by these calculations caused by "a month is not a month".
For example:

  d1=#2008-02-29#
  d2=#2010-01-31#

YearsMonthsDays(d1, d2) returns:
  1 year(s), 11 month(s), 2 day(s)          

Fn_GetDateDiffAsYMD(d1, d2) returns:
  1 Year, 11 Months, 2 Days

Both functions assume that a month's length is the length of February. I can still be in doubt if this is correct; if you had:

  d1=#2008-03-31#
  d2=#2010-01-31#

you will get a count of 1 year 10 months. 
Reducing d1 by one month (to #2008-02-29#) will increase the count to 1 year 11 months 0 (not 2) days.
But this is the way DateAdd works, and as you can't say it is wrong, I find it best - and logical - to accept this behaviour.

If you accept this, you accept that a count forward of x months may not equal a count backwards of x months.
Thus, you cannot make a reverse calculation by interchanging the variables, d1 and d2. That is why I created the function to handle both forward and backward periods.
Taking the same values, now interchanged, as an example:

  d1=#2010-01-31#
  d2=#2008-02-29#

YearsMonthsDays(d1, d2) returns:
  -1 year(s), -11 month(s), 0 day(s) 

Fn_GetDateDiffAsYMD(d1, d2) returns:
  (Minus) 1 Year, 11 Months, 2 Days

For both examples DateDiff returns a count of 23 months or 702 days.

A third example demonstrates the issue:

  d1=#2012-02-29#
  d2=#2010-01-31#

YearsMonthsDays(d1, d2) returns:
  -2 year(s), -1 month(s), 0 day(s)

Fn_GetDateDiffAsYMD(d1, d2) returns:
  (Minus) 2 Years, 1 Month

And reversing the parameters:

  d1=#2010-01-31#
  d2=#2012-02-29#

YearsMonthsDays(d1, d2)
  2 year(s), 1 month(s), 0 day(s) 

Fn_GetDateDiffAsYMD(d1, d2)
  2 Years, 1 Month

This time both functions return identical results for both directions.
Your head gets twisted and you understand why banks operate with a count of 30 days for every month.

/gustav


>>> adtp at airtelmail.in 31-03-2010 16:26 >>>
Gustav,

    Your idea of using the built in DateAdd() function is absolutely great. It ensures consistent results without needing conditional checks for various date combinations, including month ends and leap years. Thanks for coming up with this outstanding solution.

    Amongst the host of similar functions that have been attempted from time to time, yours appears to be the optimum one. 

    Another variation of YMD function, using your approach, is placed below. Results over sample tests at my end appear to be ok.

    Kevin - You could try out Gustav's function and might consider adopting it in preference to that by Chip Pearson.

Best wishes,
A.D. Tejpal
------------

' Sample function for date difference as YMD
' (It uses DateAdd approach evolved by Gustav, 
' as demonstrated in his function YearsMonthsDays())
'===================================
Public Function Fn_GetDateDiffAsYMD( _
            DtFrom As Date, _
            Optional DtUpto As Variant) As String
    ' Returns date difference as a string of
    ' Years, Months, Days
    
    ' If second argument is not supplied, it defaults
    ' to today's date - the function then serves as
    ' age calculator, with first argument being
    ' date of birth.
    
    ' If DtFrom is greater than DtUpto, the result
    ' gets prefixed with "(minus) "
    Dim Dt1 As Date, Dt2 As Date, Dt As Date
    Dim Yr As Long, Mn As Long, Dy As Long
    Dim Prefix As String
    
    If IsMissing(DtUpto) Then
        Dt = Date
    Else
        Dt = IIf(IsDate(DtUpto), DtUpto, Date)
    End If
    
    If Dt = DtFrom Then
        Fn_GetDateDiffAsYMD = "NIL"
        Exit Function
    End If
    
    ' Set Dt2 as the greater one
    If Dt > DtFrom Then
        Dt2 = Dt
        Dt1 = DtFrom
        Prefix = ""
    Else
        Dt2 = DtFrom
        Dt1 = Dt
        Prefix = "(Minus) "
    End If
    
    ' Get number of completed months
    ' Subtract 1 if Dt2 is not the last day of
    ' month and Day(Dt2) < Day(Dt1)
    Mn = DateDiff("m", Dt1, Dt2) - _
            IIf(Day(Dt2 + 1) > 1 And _
            Day(Dt2) < Day(Dt1), 1, 0)
    
    ' Get the difference of days between Dt2
    ' and projected date in last completed month
    ' (obtained by adding Mn months to Dt1)
    Dt = DateAdd("m", Mn, Dt1)
    Dy = DateDiff("d", Dt, Dt2)
    
    Yr = Mn \ 12
    Mn = Mn Mod 12
    
    Fn_GetDateDiffAsYMD = Prefix & _
                IIf(Yr > 0, Yr & _
                IIf(Yr > 1, " Years", " Year"), "") & _
                IIf(Yr > 0 And Mn > 0, ", ", "") & _
                IIf(Mn > 0, Mn & _
                IIf(Mn > 1, " Months", " Month"), "") & _
                IIf((Yr > 0 Or Mn > 0) And Dy > 0, _
                ", ", "") & IIf(Dy > 0, Dy & _
                IIf(Dy > 1, " Days", " Day"), "")
End Function
'==================================