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

I'm sure I missed somthing...but worked for the dates/results given as examples.

 

Mark A. Matte

 

'***********END*************

Dim s_dt As Date, e_dt As Date
s_dt = Me!txtStart
e_dt = Me!txtEnd
Dim Y_s_compare As Date, Y_e_compare As Date, M_compare As Date
Dim y, m, d
Y_s_compare = Format(DatePart("m", s_dt) & "/" & DatePart("d", s_dt) & "/1904", "short date")
Y_e_compare = Format(DatePart("m", e_dt) & "/" & DatePart("d", e_dt) & "/1904", "short date")

If Y_s_compare > Y_e_compare Then
    y = DateDiff("yyyy", s_dt, e_dt) - 1
Else
    y = DateDiff("yyyy", s_dt, e_dt)
End If

    If DatePart("d", DateAdd("d", 1, s_dt)) = 1 Then
        d = 0
        m = -1
        M_compare = DatePart("m", s_dt) & "/1/" & DatePart("yyyy", s_dt)
        Do Until M_compare > e_dt
            M_compare = DateAdd("m", 1, DatePart("m", M_compare) & "/1/" & DatePart("yyyy", M_compare))
            M_compare = DateAdd("m", 1, M_compare) - 1

            m = m + 1
        Loop
        m = m - y * 12
    Else
        d = DateDiff("d", s_dt, DateAdd("m", 1, DatePart("m", s_dt) & "/1/" & DatePart("yyyy", s_dt)))
        m = -1
        M_compare = DatePart("m", s_dt) & "/1/" & DatePart("yyyy", s_dt)
        Do Until M_compare >= e_dt
            M_compare = DateAdd("m", 1, DatePart("m", M_compare) & "/1/" & DatePart("yyyy", M_compare))
            M_compare = DateAdd("m", 1, M_compare) - 1

            m = m + 1
        Loop
        m = m - y * 12
    End If
    
If DatePart("d", DateAdd("d", 1, e_dt)) = 1 Then
    d = d
Else
    d = d + DateDiff("d", DatePart("m", e_dt) & "/1/" & DatePart("yyyy", e_dt), e_dt) + 1
End If

If m = 12 Then
    m = 0
    y = y + 1
End If
MsgBox "y=" & y & ",m=" & m & ",d=" & d
'***********END************* 

 

 
> Date: Mon, 5 Apr 2010 22:48:24 +0200
> From: Gustav at cactus.dk
> To: accessd at databaseadvisors.com
> 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
> 
> 
> >>> adtp at airtelmail.in 05-04-2010 21:14 >>>
> 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
> ------------
> 
 		 	   		  
_________________________________________________________________
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. 
http://www.windowslive.com/campaign/thenewbusy?tile=multicalendar&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5