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

Hi A.D.

This is a tough one because it is not easy to explain what exactly is counted or added.

First, giving DateAdd a second thought, I think that we have to conclude - based on your example 2.2.2 - that it is not strictly intended for this purpose, only for a sort of arithmetic handling of dates according to this rule:

  1.  Add n to the numeric value of the month of the date parameter.
  2. Adjust the day of the resulting month if an invalid date is the result. For example, a resulting date of Apr. 31. will be adjusted to Apr. 30.

Second, when we count years, months, and days, the day counts of the leading and trailing month should be equal to one month respectively.

This simple definition would turn the result of your example 2.2.2 into "1 month 0 days" which I believe is how most people would measure such an interval.
I think this could clear up the matter and remove confusion.

/gustav


>>> adtp at airtelmail.in 05-04-2010 21:14 >>>
Hi Gustav,

    I agree. It would be preferable to let VBA's built in date logic take care of all pertinent factors (e.g. month ending dates, leap years as well as varying days in different months and different years), completely eliminating the need for imposing any external logic in this regard. This would provide an output independent of subjective interpretations or preferences across developers.

    Considering the fact that adding whole number of months to Date1 via DateAdd() function shrink fits the day part of projected date so as not to exceed the maximum available number of days in the target month, it follows that the elapsed period involving month ending dates would conform to the following pattern:

    1 - (Day part of Date1) >= (Day part of Date2)
    ------------------------------------------------
    1.1 - Positive progression (Date2 >= Date1):
    1.1.1 - 31-Jan-2003 to 28-Feb-2003 = 1 month
    1.1.2 - 31-Mar-2003 to 30-Apr-2003 = 1 month
    1.1.3 - 30-Apr-2003 to 30-Sep-2003 = 5 months

    1.2 - Negative progression (Date2 < Date1):
    1.2.1 - 31-Mar-2003 to 28-Feb-2003 = -1 month
    1.2.2 - 31-May-2003 to 30-Apr-2003 = -1 month
    1.2.3 - 30-Sep-2003 to 30-Apr-2003 = -5 months

    2 - (Day part of Date1) < (Day part of Date2)
    -----------------------------------------------
    2.1 - Positive progression (Date2 >= Date1):
    2.1.1 - 28-Feb-2003 to 31-Mar-2003 = 1 month, 3 days
                (28-Feb-2003 plus 1 month = 28-Mar-2003)
    2.1.2 - 30-Apr-2003 to 31-May-2003 = 1 month, 1 day
                (30-Apr-2003 plus 1 month = 30-May-2003)

    2.2 - Negative progression (Date2 < Date1):
    2.2.1 - 28-Feb-2003 to 31-Jan-2003 = -28 days
                (28-Feb-2003 minus 1 month would have given
                28-Jan-2003, which would be out of range - 
                so, DateDiff() applied directly)
    2.2.2 - 30-Apr-2003 to 31-Mar-2003 = -30 days
                (30-Apr-2003 minus 1 month would have given
                30-Mar-2003, which would be out of range - 
                so, DateDiff() applied directly)

    You might like to examine para 2.2 above and confirm so that the YMD function could be fine tuned accordingly.

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

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