DateDiff Calculation Off By One Year???

[Ronster]

I'm running a query that requires that I convert a YYYYMMDD text
formated field to a valid date, then use the converted date to
determine the person's current age. I'm creating a new field with this
expression:

Assume today's date is 7/12/06 and Date of Birth to convert is
19651204, then I run it through the following formula:

DateDiff("yyyy",CVDate(Mid$([Date of Birth],5,2) & "/" & Mid$([Date of
Birth],7,2) & "/" & Mid$([Date of Birth],1,4)),Date())

Calculated Age shows 41

Yes, 2006 - 1965 does = 41 BUT his birthday is on Decemeber 12th. The
calculated age should show 40. All the dates past 7/12/06 show this
extra year.

I tried creating separate fields for the converted DOB and Today's
date, then run DateDiff on just these fields but I get the same result.
Ran the same dates in Excel with DateDif and get 40 years old, all
dates were correct.

Anybody have any ideas why this is?

 

[Rick Brandt]

I'm running a query that requires that I convert a YYYYMMDD text
formated field to a valid date, then use the converted date to
determine the person's current age. I'm creating a new field with
this expression:

Assume today's date is 7/12/06 and Date of Birth to convert is
19651204, then I run it through the following formula:

DateDiff("yyyy",CVDate(Mid$([Date of Birth],5,2) & "/" & Mid$([Date of
Birth],7,2) & "/" & Mid$([Date of Birth],1,4)),Date())

Calculated Age shows 41

Yes, 2006 - 1965 does = 41 BUT his birthday is on Decemeber 12th. The
calculated age should show 40. All the dates past 7/12/06 show this
extra year.

I tried creating separate fields for the converted DOB and Today's
date, then run DateDiff on just these fields but I get the same
result. Ran the same dates in Excel with DateDif and get 40 years
old, all dates were correct.

Anybody have any ideas why this is?

Frequently posted question.

DateDiff counts "boundaries crossed". That means DateDiff() will indicate that
there is one year difference between 12/31/2000 and 1/1/2001 even though they
are only one day apart. You need a custom expression that takes into account
whether the person's birthday has occurred yet in the current calendar year.

If you Google these groups on "calculate age from birth date" you should find
numerous examples or links to examples.

 

[John Vinson]

Yes, 2006 - 1965 does = 41 BUT his birthday is on Decemeber 12th. The
calculated age should show 40. All the dates past 7/12/06 show this
extra year.

That's how DateDiff works: it counts *year boundaries*. If you try
DateDiff("yyyy", #12/31/2005#, #1/1/2006#) it will show one year; if
you use DateDiff("yyyy", #1/1/2006#, #12/31/2006#) it will show zero.

A birthday-sensitive Age expression is

Age: DateDiff("yyyy", [DateOfBirth], Date()) -
IIF(Format([DateOfBirth], "mmdd") > Format(Date(), "mmdd"), 1, 0)

to subtract one year if the birthday has not yet arrived.

John W. Vinson[MVP]

 

[Ronster]

Thanks guys, this worked out great! I am a newbie to Access and should
assume most of my problems someone else has already had so I'll look
around more next time.

Thanks again.

Yes, 2006 - 1965 does = 41 BUT his birthday is on Decemeber 12th. The
calculated age should show 40. All the dates past 7/12/06 show this
extra year.

That's how DateDiff works: it counts *year boundaries*. If you try
DateDiff("yyyy", #12/31/2005#, #1/1/2006#) it will show one year; if
you use DateDiff("yyyy", #1/1/2006#, #12/31/2006#) it will show zero.

A birthday-sensitive Age expression is

Age: DateDiff("yyyy", [DateOfBirth], Date()) -
IIF(Format([DateOfBirth], "mmdd") > Format(Date(), "mmdd"), 1, 0)

to subtract one year if the birthday has not yet arrived.

John W. Vinson[MVP]

 

[Per Larsen]

That's how DateDiff works: it counts *year boundaries*. If you try
DateDiff("yyyy", #12/31/2005#, #1/1/2006#) it will show one year; if
you use DateDiff("yyyy", #1/1/2006#, #12/31/2006#) it will show zero.

That's right, but if you use DateDiff("d", ...) it shows *days boundaries*. So if you divide the value of DateDiff counting days, you could divide the result with the number of days in a year, i.e. 365...

But then comes the problem with one leap years each fourth year, so you'll have to divide by 365.25...

Then again, every 100 year is *not* a leap year (i.e. Year mod 100 = 0), but it is if Year mod 400 = 0.

All together, if you divide your DateDiff with the number of days the last 400 years (Days400) and use the integer part of the result, you should be fine (not?):

Days400 = DateDiff("d", DateAdd("yyyy", -400, Date()), Date())/400 = 365.2425


Int(DateDiff("d", #12/4/1965#, #12/3/2006#)/(DateDiff("d", DateAdd("yyyy", -400, Date()), Date())/400)) = 40

Int(DateDiff("d", #12/4/1965#, #12/4/2006#)/(DateDiff("d", DateAdd("yyyy", -400, Date()), Date())/400)) = 41


PerL

 

[John Vinson]

All together, if you divide your DateDiff with the number of days the last 400 years (Days400) and use the integer part of the result, you should be fine (not?):

Why go to all the hassle?

Just subtract one year if the birthday has not yet arrived:

DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

John W. Vinson[MVP]