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

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