DateDiff Calculation Off By One Year???

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ronster - 14 Jul 2006 00:43 GMT

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?

Reply to this Message

Rick Brandt - 14 Jul 2006 00:54 GMT

> 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
[quoted text clipped - 19 lines]
>
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

John Vinson - 14 Jul 2006 00:59 GMT

>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]

Reply to this Message

Ronster - 14 Jul 2006 21:54 GMT

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
[quoted text clipped - 12 lines]
>
> John W. Vinson[MVP]

Reply to this Message

Per Larsen - 17 Jul 2006 15:47 GMT

>> 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
[quoted text clipped - 3 lines]
> 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

Reply to this Message

John Vinson - 17 Jul 2006 18:14 GMT

>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]

Reply to this Message