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

Hi A.D.

You are right about the behaviour of DateAdd. In- or decrementing by one month will always return a date in the next or prior month.

However, it doesn't take into account if the date in question is ultimo of the month. That's by design, though in quite a few cases you would say that in any case adding "one month" to ultimo of one month will return ultimo of the next month and vice versa.
This is what my function Months do:

<code>
Public Function Months( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date) _
  As Integer

' Returns the difference in full months between datDate1 and datDate2.
'
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
'
' Gustav Brock, Cactus Data ApS.
' 2000-12-20.

  Dim intDay1           As Integer
  Dim intDay2           As Integer
  Dim intMonths         As Integer
  Dim intDaysDiff       As Integer
  Dim intReversed       As Integer
  
  ' No special error handling.
  On Error Resume Next
  
  intMonths = DateDiff("m", datDate1, datDate2)
  If intMonths = 0 Then
    ' Both dates fall within the same month.
  Else
    intDay1 = Day(datDate1)
    intDay2 = Day(datDate2)
    If Month(datDate1) < Month(DateAdd("d", 1, datDate1)) Then
      ' Date datDate1 is ultimo.
      ' Decrease date datDate2 if day of datDate2 is higher.
      If intDay2 > intDay1 Then
        datDate2 = DateAdd("d", intDay1 - intDay2, datDate2)
        intDay2 = Day(datDate2)
      End If
    End If
    If Month(datDate2) < Month(DateAdd("d", 1, datDate2)) Then
      ' Date datDate2 is ultimo.
      ' Decrease date datDate1 if day of datDate1 is higher.
      If intDay1 > intDay2 Then
        datDate1 = DateAdd("d", intDay2 - intDay1, datDate1)
        intDay1 = Day(datDate1)
      End If
    End If
    ' Calculate day difference.
    intDaysDiff = intDay1 - intDay2
    intReversed = Sgn(intMonths)
    ' Decrease count of months by one if dates are closer than one month.
    intMonths = intMonths - (intReversed * Abs((intReversed * intDaysDiff) > 0))
  End If
  
  Months = intMonths
  
End Function
</code>

One could argue that this is the method to follow. For example, consider the results of DateAdd:

DateAdd("m", 1, #2010-01-31#) => 2010-02-28
DateAdd("m", 1, #2010-02-28#) => 2010-03-28

while:
DateAdd("m", 2, #2010-01-31#) => 2010-03-31

so adding 1 + 1 month is different from adding 2 months.

I think the YMD count should either closely follow the rules of DateAdd or the above method which would return identical results for reversed dates. 

/gustav
 

>>> adtp at airtelmail.in 01-04-2010 20:39 >>>
Gustav,

    Though apparently optimum options amongst various potential alternatives, neither of the two methods, i.e. forward or backward DateAdd() computation can be considered completely perfect. Each represents a degree of compromise inherent in the built-in date arithmetic. 

    This stems from the way VBA handles addition (or subtraction) of given number of months to (or from) a month ending start date. The magic of rounding off the computed finish date to a month end date takes place only if the projected finish date happens to be >= the end date for the finish month.

    For example, adding 1 month to 31-Jan-2010 gets 28-Feb-2010. On the other hand, adding 1 month to 28-Feb-2010 fetches only 28-Mar-2010 (not 31-Mar-2010). Similarly, adding 1 month to 31-Mar-2010 gets 30-Apr-2010, while addition of 1 month to 30-Apr-2010 gets only 30-May-2010 (not 31-May-2010).

    Going backwards, subtracting 1 month from 31-Mar-2010 gets 28-Feb-2010, while doing similar subtraction from 28-Feb-2010 fetches 28-Jan-2010 (not 31-Jan-2010). As an interesting example, if 1 month is subtracted from 31-Mar-2010 and then 1 month is added to the result, we get 28-Mar-2010. Thus for net subtraction/addition of zero month, the starting date value stands reduced by 3. This brings out a weird aspect of a set of DateAdd() operations involving positive and negative values for month argument.

    Considering the fact that time flow is always in forward direction, won't it help simplify matters if it were agreed as a standing practice that elapsed period between any two dates would always be reckoned from lower date to the higher one? Minus qualifier in the result would merely be indicative of the fact that the user has supplied the date arguments in reverse order (inadvertently or by design). That way, the computed difference between any two dates would always be consistent. 

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

  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Thursday, April 01, 2010 00:16
  Subject: Re: [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