DateDiff Function

[rogerarce]

Hi. I have this on Excel:
=DATEDIF(C9,C10,"y&quot&" Years, "& DATEDIF(C9,C10,"ym&quot&
" Months, " & DATEDIF(C9,C10,"md&quot & " Days"

C9 = date1
c10 = date2

I was wondering how to make this formula using
DateDiff in access? Is this possible?

Thanks in advanced for your help.

Roger

 

[WildHare]

Not only possible, but much easier.

In Access, the DATEDIFF function will return whatever INTERVAL you ask for, between two dates.

vMonths = DateDiff("m", Date1, Date2)
vDays = DateDiff("d", Date1, Date2)

This should be enough to get you started.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[rogerarce]

I see how it works, but tell me: I want to display
in 3 boxes the following:
dates to use: 01/01/2000 and 01/10/03

Wish to display:
box1 3 year
box2 0 months
box3 9 days

but the code displays:
box1 3 year
box2 36 months
box3 1105 days

I know this can be avoided using ym or md but in excel.
Any ideas? Thank you very much for your first replay.

Roger

 

[WildHare]

Ah yes...You'll need to add each major unit to the start date as you go along, in order to do the next calculation.

Start : 1/1/2000
End : 1/9/2003

Xyears: DateDiff(&quot;yyyy&quot;, Start, End) <--- three years

Start = DateAdd(&quot;y&quot;, xYears, Start) <-- add the three years to the start guy - you might check the syntax for this, I'm not sure it's correct.

XMonths: DateDiff(&quot;m&quot;, Start, End) <--- this gives us the 0 (zero)

Start = DateAdd(&quot;m&quot;, xMonths, Start) <-- add the Zero months to start

xDays = DateDiff(&quot;d&quot;, Start, End) <-- this gives us the 9 days.

xYears : 3
xMonths: 0
xDays: 9

I suppose you could do it in one big complex calculation as well, but this three-stepper should be clear.

Jim


Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MichaelRed]

hmmmmmmmmmm,

I hesitate to offer this, as it is domewhat different than what is requested, it does return all of the info requested, but it is in a single string:

Public Function basDOB2AgeExt(DOB As Date, Optional AsOf As Date = -1) As String


    'Michael Red    5/23/02
    'Dob is just the date of Birth
    'AsOf is an optional date to check - as in examples 1 & 2
    'Otherwise, the DOB is checked against the Current Date

    '? basDOB2AgeExt(#8/21/42#)
    '59 Years 9 Months and 2 Days.

    '? basDOB2AgeExt(#8/21/1942#, #8/20/2022#)
    '79 Years 11 Months and 30 Days.

    '? basDOB2AgeExt(#8/21/1942#, #8/21/2022#)
    '80 Years 0 Months and 0 Days.

    Dim tmpAge As Integer       'Simple DateDiff w/o Birthday correction
    Dim tmpDt As Date           'Date to use in intermediate Calcs
    Dim DtCorr As Boolean       'BirthDay Before or After date in question
    Dim YrsAge As Integer
    Dim MnthsAge As Integer     'Additional Mnths
    Dim DaysAge As Integer      'Additional Days

    If (AsOf = -1) Then         'Check for (Optional Date to Check against)
        AsOf = Date             'If Not Supplied, Assume Today
    End If

    tmpAge = DateDiff("YYYY", DOB, AsOf)        'Just the Years considering Jan 1, Mam
    DtCorr = DateSerial(Year(AsOf), Month(DOB), Day(DOB)) > AsOf

    YrsAge = tmpAge + DtCorr           'Just Years and Correction
    tmpDt = DateAdd("yyyy", YrsAge, DOB)

    MnthsAge = DateDiff("m", tmpDt, AsOf)
    DtCorr = DateAdd("m", MnthsAge, tmpDt) > AsOf
    MnthsAge = MnthsAge + DtCorr

    tmpDt = DateAdd("m", MnthsAge, tmpDt)
    DaysAge = DateDiff("d", tmpDt, AsOf)

    basDOB2AgeExt = YrsAge & " Years " & MnthsAge & " Months and " & DaysAge & " Days."

End Function

I suppose an intrepid soul could re-work it to return a small array of integers ...


MichaelRed
m.red@att.net

Searching for employment in all the wrong places