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

A.D.

I use Chip Pearson's Age Function
(http://www.cpearson.com/Excel/DateTimeVBA.htm)
Works well in Excel, Access, etc

Function fnAGE(Date1 As Date, Date2 As Date) As String
    Dim Y                                         As Integer
    Dim M                                         As Integer
    Dim D                                         As Integer
    Dim Temp1                                     As Date
    Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
    Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
    M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
    D = Day(Date2) - Day(Date1)
    If D < 0 Then
        M = M - 1
        D = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + D + 1
    End If
    fnAGE = Y & " years " & M & " months " & D & " days"
End Function

Kevin Waddle


thewaddles at sbcglobal.net
When you are in the dark, listen, and God will give you a very precious
message.. ~ Oswald Chambers
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D. Tejpal
Sent: Sunday, March 28, 2010 9:42 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Date Difference As Completed Years-Months-Days

    Feasibility of a universal function for calculation of date difference
in terms of completed years, months and days has often featured in various
discussion groups. It is also referred as age calculation function when
second argument is optional and defaults to today's date.

    Over the years, different flavors of such a function have been
attempted. In this regard, it would be desirable to evolve a consensus as to
the governing rules to be followed for computing the results. 

    Proposed draft guidelines are placed below (Let Dy1 & Dy2 be the day
parts of start & end dates respectively. Let Dy represent days count in
final result):

    ==========================================
    1 - If both Dy1 & Dy2 represent ends of respective months, day part of
the final result (Dy) is zero.
    2 - If Dy2 is at the end of month and Dy1 is not, Dy equals unfinished
days in start month.
    3 - If Dy1 is at the end of month and Dy2 is not, Dy equals Dy2.
    4 - If neither date represents end of the month, and Dy2 >= Dy1, result
days (Dy) = Dy2 - Dy1.
    5 - If neither date represents end of the month, and Dy2 < Dy1, result
days (Dy) is computed by adding Dy2 and unfinished days in start month.
    ==========================================

    It would be nice, if interested members could kindly examine the above
and offer their considered views regarding modifications to the draft
guidelines, as felt necessary.

Best wishes,
A.D. Tejpal
------------
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com