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

Hi A.D.

I can see I missed your point in my first posting ...

However, to handle the 30/31 days problem (not to say the 28/29 days of February) when counting months and days, I have redesigned my old Months function to work the same way as the Years function:

<code>
Public Function Months( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date, _
  Optional ByVal booLinear As Boolean) _
  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)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of month counts.
' For a given datDate1, if datDate2 is decreased stepwise one month from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
'   3, 2, 1, 0,  0, -1, -2
' If booLinear is True, the sequence will be:
'   3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().

' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of months to dates of Feb. 29.
' when the resulting year is a common year.
'
' 2010-03-30. Cactus Data ApS, CPH.

  Dim intDiff   As Integer
  Dim intSign   As Integer
  Dim intMonths As Integer
  
  ' Find difference in calendar months.
  intMonths = DateDiff("m", datDate1, datDate2)
  ' For positive resp. negative intervals, check if the second date
  ' falls before, on, or after the crossing date for a 1 month period
  ' while at the same time correcting for February 29. of leap years.
  If DateDiff("d", datDate1, datDate2) > 0 Then
    intSign = Sgn(DateDiff("d", DateAdd("m", intMonths, datDate1), datDate2))
    intDiff = Abs(intSign < 0)
  Else
    intSign = Sgn(DateDiff("d", DateAdd("m", -intMonths, datDate2), datDate1))
    If intSign <> 0 Then
      ' Offset negative count of months to continuous sequence if requested.
      intDiff = Abs(booLinear)
    End If
    intDiff = intDiff - Abs(intSign < 0)
  End If
  
  ' Return count of months as count of full 1 month periods.
  Months = intMonths - intDiff
  
End Function
</code>

Further, by counting the total months this can easily be converted to full years and remaining months
Now, all that is left is to find the count of remaining days. This is not easy as it will be zero for many date combinations where both dates are at ultimo of a month, for example for both of these intervals:

  From 2015-03-30 to 2016-04-30
  From 2015-03-31 to 2016-04-30

But I've created a function that does this:

<code>
Public Function YearsMonthsDays( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date, _
  Optional ByRef lngYears As Long, _
  Optional ByRef lngMonths As Long, _
  Optional ByRef lngDays As Long) _
  As String
  
' Returns the difference in years, months, and days 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.
' 2010-03-30.

  ' Count of months in a calendar year.
  Const cintMonths  As Integer = 12
  
  Dim datDateMonth  As Date
  Dim intDays       As Integer
  
  ' No special error handling.
  On Error Resume Next
  
  lngMonths = Months(datDate1, datDate2)
  
  datDateMonth = DateAdd("m", lngMonths, datDate1)
  lngDays = DateDiff("d", datDateMonth, datDate2)
  intDays = Sgn(lngDays)
  If intDays <> 0 Then
    If intDays <> Sgn(DateDiff("d", datDate1, datDate2)) Then
      lngDays = 0
    End If
  End If
  
  lngYears = lngMonths \ cintMonths
  lngMonths = lngMonths Mod cintMonths
  
  YearsMonthsDays = CStr(lngYears) & " year(s), " & CStr(lngMonths) & " month(s), " & CStr(lngDays) & " day(s)"

End Function
</code>

It will even count correctly for negative periods - where date1 is later than date2.
To allow for general use, it also returns ByRef the count of years, months, and days while the normal return value is a formatted string like:

  12 year(s), 7 month(s), 3 day(s)

I think this should fulfil your requirements.

/gustav


>>> adtp at airtelmail.in 30-03-2010 11:35 >>>
Gustav,

    Thanks for such interesting insight. For this thread, let us say that the result is to be returned as a self contained string of completed years, months and days (in style: y years, m months, d days). 

    Kevin has provided Chip Pearson's Age function, which we could examine further. Would you be in a position to suggest a suitable function, duly taking into account the various factors outlined in your post.

    Ideally, the proposed function should be able to  handle all types of special date combinations that are not straightaway amenable to application of fixed formula. Some examples:
    
    30-Apr-2005 (Month End) to 31-Mar-2009 (Month End) should resolve to 3 years, 11 months, 0 days (8 complete months in 2005, plus 3 complete years 2006 to 2008, plus 3 complete months in 2009).
    (However, as per Chip Pearson's function posted by Kevin, it evaluates to 3 years 11 months 1 days)

    30-Apr-2005 (Month End) to 30-Mar-2009 should resolve to 3 years, 10 months, 30 days (8 complete months in 2005, plus 3 complete years 2006 to 2008, plus 2 complete months in 2009, plus 30 elapsed days of Mar-2009).
    (However, as per Chip Pearson's function posted by Kevin, it evaluates to 3 years 11 months 0 days)

    28-Feb-2005 (Month End) to 29-Feb-2008 (Month End) should resolve to 3 years, 0 months, 0 days (10 complete months in 2005, plus 2 complete years 2006 to 2007, plus 2 complete months in 2008).
    (However, as per Chip Pearson's function posted by Kevin, it evaluates to 3 years 0 months 1 days)

    29-Feb-2008 (Month End) to 28-Feb-2010 (Month End) should resolve to 2 years, 0 months, 0 days (10 complete months in 2008, plus 1 complete year 2009, plus 2 complete months in 2010).
    (However, as per Chip Pearson's function posted by Kevin, it evaluates to 1 years 11 months 28 days)

    Other interested members might also like to kindly offer their views.

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

  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Monday, March 29, 2010 12:36
  Subject: Re: [AccessD] Date Difference As Completed Years-Months-Days


  Hi A.D.

  <<Snipped>>

  The main problem when calculation age in years is that a month is not a month and a year is not a year. Both have varying count of days. 

  This can lead to many worries until you realise that the best method is to turn it upside down - by adding a found interval of years (age) to the first date to prove that the second date is the right. That could lead to a new problem if you should consider how to add years but that is not the case as VBA features the DateAdd function which calculates correctly. Thus:

    Age = Years(Date1, Date2) <=> Date2 = DateAdd("yyyy", Age, Date1)

  So the simple answer to your question is to apply DateAdd to check your calculation and correct when needed. After a lengthy discussion and input from several members at Experts Exchange which also introduced the topic "Linear Age", this is how it turned out:

  <<Snipped>>

  /gustav