Albert,
Sounds like a fun application.
Here's one way. You'll have to work very hard to find the very occasional
case where it is off by a day for the driver who is 15 on race day. I don't
think you can make it fail for the driver who turns 9 on race day. How often
are race days on leap day?
(INT( (raceDate - BirthDate) / 365.25))
If you have to really make absolutely sure about those two cases and the
leap year math, then you'll have to break out month, day, and year from each
date, and compare the month and day as well as the year.
Age if birthday month and day is before race day:
( (IYR4(racedate)) - (IYR4(birthdate)) )
And then, subtract a year from age if race day is before birthday: ("If
integer day of year of birthdate is great than integer day of year of
racedate, subtract 1, else subtract zero")
( (IYR4(racedate)) - (IYR4(birthdate)) - (IFGT( (IDOY(birthdate)),
(IDOY(racedate)), 1, 0 )) )
But a racedate or a birthdate in a leap year, combined with a race date
within 1 day of the birthday could still screw you up, so you might have to
make this more complicated with IMON and IDAY.
Bill
Bill
On Thu, Jun 3, 2010 at 2:23 PM, Albert Berry <[email protected]> wrote:
> Has anyone encountered the situation where the exact age is required for
> eligibility?
>
> I am working on something that requires a child's age to be between 9 and
> 14 on a particular race date. I want to be able to display the age as of the
> date of a race. The rules are that
>
> 01 9th birthday is on or before race day.
> 02 15th birthday is after race day, not on race day.
>
> That's easy to handle as an eligible/ineligible switch. What I want to do
> is to display the age as an integer number by comparing the race date with
> the birth date.I can do it in my head as all of you can, but my little grey
> cells are on strike. I just cannot figure out a simple method of displaying
> this value using a single formula. Example follows.
>
> Birth Date: 6/11/2000 + Race Date: 6/10/2010 = 9 years old.
> Birth Date: 6/10/2000 + Race Date: 6/10/2010 = 10 years old.
> Birth Date: 6/09/2000 + Race Date: 6/10/2010 = 10 years old.
>
> Data sources:
> Races.Race = Cars.Race -- PK < FK
> Drivers.DriverID = Cars.DriverID -- PK < FK
> Races.RaceDate
> Drivers.DriverBirthDate
>
> Age goes here: Cars.Age
>
> The same driver can race in multiple years and multiple races within a
> year. The Cars table will list the car and driver in a separate data row for
> each race. The age on the specific date of the race is the necessary integer
> value
>
> All help gratefully received.
>
> Albert
>
>
>
