Thanks for the input, Bill. This is for a Soap Box Derby as sponsored by
the Knights of Columbus. I ran it last year, but this year it seems that
they want to up the thing a little, so we are wrapping in all the
finances, allowing for 3 best of 4 heats, and such like interesting
little details.
They were so happy with my volunteer work (yeah, I'm a KC) that I now
have to allow for an adult race as well, and it is a knockout one. Fun!
Albert
Bill Downall wrote:
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]
<mailto:[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
