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

A.D.

 

Quick question:  When you give the example for:

"> 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)"

 

Does 30-Apr-2005 count as a day?...or does each date reflect the end of that date?(0 days vs 1 day)

 

Thanks,

 

Mark A. Matte


 
> From: adtp at airtelmail.in
> To: accessd at databaseadvisors.com
> Date: Tue, 30 Mar 2010 15:05:39 +0530
> Subject: Re: [AccessD] Date Difference As Completed Years-Months-Days
> 
> 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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
 		 	   		  
_________________________________________________________________
Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1