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

A.D. Tejpal adtp at airtelmail.in
Tue Mar 30 04:35:39 CDT 2010


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


More information about the AccessD mailing list