Age Calculation

[bmiller310]

I've used a formula to calculate age in a query. The only formula that works is one that uses the "now" feature. I want to calculate the age of a person based on a defined date. For example: I need to know how old a person is on or before May 1, 2012. This is a youth athletic association so it has to be precise. We usually print registration information in January. This formula works, but I'm not sure where to put "5/1/2012". Thanks.

CalAge: DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

[bigroo]

Hi bmiller310,

I will take a guess and say place 05/01/2012

CalAge: DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

Here:

CalAge: DateDiff("yyyy",[DOB],#05/01/2012#)+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

Someone correct me if I am wrong!!

[bmiller310]

I just tried that. It still calculates using the current date. Any other suggestions.

[RayMilhon]

Change it to
DateDiff("yyyy",,[dob],#05/01/2012#)+(#05/01/2012#>DateSerial(Year(Date()),Month([dob]),Day([dob])))

[RayMilhon]

Although the #05/01/2012# should actually be CVDate("05/12/" & Year(date())) That way you don't have to change it each year.

[ssanfu]

If this is for some event: tournament, race, etc., you probably have the date of the event stored somewhere. You could reference that date in the age calculation, in case you have multiple events during the year.

[bigroo]

Thanks RayMilhon,

I did not see the second Date() reference in the formula!!
Thanks

[bmiller310]

Thanks guys, this works great. If I had an event date called EDate where would I put it in the formula.

[RayMilhon]

The later date should be second. If the later date is first you will get a negative number

For example

DateDiff("d", #11/01/2011#, #11/02/2011#) = 1
DateDiff("d",#11/02/2011#, #11/01/2011#) = -1

[bmiller310]

Found another problem with this formula:

When the DOB is 1/2/2000 and I use the formula:

CalAge: DateDiff("yyyy",[dob],#5/1/2012#)+(#5/1/2012#>DateSerial(Year(Date()),Month([dob]),Day([dob])))
I get an answer of 11 instead of 12. Any suggestions?

[RayMilhon]

Here's what's happening.
DateDiff("yyyy",[dob],#05/01/2012#) = 12
#05/01/2012#>DateSerial(Year(date()),Month([dob]),Day([dob]))) = True = -1
12+(-1)=11

What you're statement says is if the date 05/01/2012 Is > the persons birthday subtract 1 from the age.

[bmiller310]

So I guess I should change the second part to say "month"??? I'm not sure here. Thanks.

[RayMilhon]

First let's look at the 2 possibilites

1. DOB = 1/2/2000
2. DOB = 12/2/2000

Case 1 their age on 05/01/2012 is 12
Case 2 their age on 05/01/2012 is 11

Part 1 of the formula
Datediff("yyyy",[dob],#05/01/2012#)

Case 1 = 12 should be True
Case 2 = 12 should be false

2nd part of the formula
+(#05/01/2012>DateSerial(Year(date()),Month([dob]),Day([dob]))))

Case 1 Dateserial(Year(date()),MOnth([dob]),Day([dob])) = DateSerial(2012,1,2) so the formula = (#05/01/2012#>#01/02/2012#) = True = -1

Case 2 Dateserial(Year(date()),MOnth([dob]),Day([dob])) = DateSerial(2012,12,2) so the formula = (#05/01/2012#>#12/02/2012#) = False = 0

Results
Case 1 = 12 + (-1) = 11
Case 2 = 12 + 0 = 12

So the Correct formula is

CalAge: DateDiff("yyyy",[dob],#5/1/2012#)+(#5/1/2012#<DateSerial(Year(Date()),Month([dob]),Day([dob])))

[bmiller310]

I think I figured it out. It's a little different formula, but I think it works:

CalAge: DateDiff("yyyy",[DOB],#9/1/2012#)-IIf(Format([DOB],"mmdd")>Format(#9/1/2012#,"mmdd"),1,0)