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

A.D. Tejpal adtp at airtelmail.in
Wed Apr 7 00:27:06 CDT 2010


Hi Gustav,

    It is observed that if we provide a SnapFit feature for situations where day part of month ending Dt1 is <= day part of Dt2, the results become better streamlined, free of erstwhile irritants. This is because a two way symmetry comes into effect, as VBA already has built-in SnapFit feature for situations where day part of Dt1 is >= day part of month ending Dt2.

    Some sample results, after incorporation of this feature are placed below:
=================================
Dt1                    Dt2                   YMD
=================================
1 - Positive Progression (Dt2 > Dt1):
      Day(Dt1) >= Day(MonthEndDt2)
      (VBA's built-in SnapFit action comes into play)
--------------------------------------------------
28-Jan-2003    28-Feb-2003    1 Month
29-Jan-2003    28-Feb-2003    1 Month
30-Jan-2003    28-Feb-2003    1 Month
31-Jan-2003    28-Feb-2003    1 Month
--------------------------------------------------

2 - Positive Progression (Dt2 > Dt1):
      Day(MonthEndDt1) <= Day(Dt2)
      (User defined SnapFit action comes into play)
      Note: VBA does not provide SnapFit for such
                date combinations 
--------------------------------------------------
28-Feb-2003    28-Mar-2003    1 Month
28-Feb-2003    29-Mar-2003    1 Month
28-Feb-2003    30-Mar-2003    1 Month
28-Feb-2003    31-Mar-2003    1 Month
--------------------------------------------------

3 - Negative Progression (Dt2 < Dt1):
      Day(Dt1) >= Day(MonthEndDt2)
      (VBA's built-in SnapFit action comes into play)
--------------------------------------------------
28-Mar-2003    28-Feb-2003    (Minus) 1 Month
29-Mar-2003    28-Feb-2003    (Minus) 1 Month
30-Mar-2003    28-Feb-2003    (Minus) 1 Month
31-Mar-2003    28-Feb-2003    (Minus) 1 Month
--------------------------------------------------

4 - Negative Progression (Dt2 < Dt1):
      Day(MonthEndDt1) <= Day(Dt2)
      (User defined SnapFit action comes into play)
      Note: VBA does not provide SnapFit for such
                date combinations 
--------------------------------------------------
28-Feb-2003    28-Jan-2003    (Minus) 1 Month
28-Feb-2003    29-Jan-2003    (Minus) 1 Month
28-Feb-2003    30-Jan-2003    (Minus) 1 Month
28-Feb-2003    31-Jan-2003    (Minus) 1 Month
--------------------------------------------------

    As would be seen, the suggested feature not only takes care of adverse date combinations in negative progression, but also gets rid of a prominent irritant in positive progression. Taking the sample date combination cited in one of your earlier posts (of 01-Apr-2010):

31-Mar-2008  to  31-Jan-2010  =  1 Year, 10 Months

On reducing the start date by 1 month, we get:
(a) As per natural VBA:
29-Feb-2008    31-Jan-2010    1 Year, 11 Months, 2 Days

(b) On applying user defined SnapFit feature:
29-Feb-2008    31-Jan-2010    1 Year, 11 Months

    You might like to examine the pattern of results presented above and confirm whether it can be regarded as meriting greater acceptability. I could then follow up with the code pertaining to SnapFit action.

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

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


More information about the AccessD mailing list