I am looking for a formula that will calculate what a persons age will be on a specific date based on his DOB. We have athletes who's league age is determined on what their age is on 7/31. Example, a player who is born on 12/29/2002 is league age 9.
Try this.
var date AgeTestDate = Date(Year(Today()),7,31);
var date BirthdayThisYear = Date(Year(Today()),Month([DOB]),Day([DOB]));
Year($AgeTestDate)-Year([DOB])
-
If($BirthdayThisYear>=$AgeTestDate,1,0)
Note that the ages will jump by one year on January 1st of each year automatically.
Mark Shnier
You can't use a formula that includes the Today() function because the intent of the formula is to capture the participant's age for a particular year, school session or season. Using Today() will cause the formula to change values as time progresses. Instead you need to introduce another input reflecting a particular year, school session or season and derive the which year the 7/31 cutoff date refers to.
Bare with me as I am not a programmer but I am not sure that I totally follow. If I the age cutoff is 7/31 of this year to determine league age and I have a player who's birthday is 12/29/2002 then his league age would be 9. Whatever a players age is as of 7/31 is what we go off of.
Based on this, would the above formula offered by Mark still not be valid and if not, what should change?
Thanks again
When we discuss a cutoff date for a sports season we are implicitly assuming everyone is talking about the current, upcoming or a specific season. Mark's formula needs to be modified to determine the cutoff year from some other field in your table that identifies the season the player in participating or qualifing for. Perhaps you have a field in your table with values such as "2011/2012", "Spring 2012" or simply "2012". This field should be used to determine the year which feeds Mark's formula. If you use Today() as a proxy for identifying a specific season the formula will only be accurate during that specific season - it will not be accurate in the future.
Yes, my formula assumes the simple case, that its a calendar year roll over - which in fact for a little league type operation is probably good enough. It also has some intuativeness for low tech users as it answers the question as to the kids' ages for the current season. "How old will the kid be in the summer". ie the summer of the year you are in.
Yes, if the league planning for the 2013 season starts in the fall of the 2012 year, there could another enhancement to the formula for a cut off as to when the year rolls over (as opposed to a calendar year roll over). But in reality, users will know that next year the the kids will all be a year older so if the kid is only showing 8 years old at Chrstmas, then they are good to play next year because on January 1st, all the ages will advance by 1 year.
Mark Shnier
It will never work with such simplicity. Kids today are on multiple teams with overlapping seasons (indoor, outdoor, travel, recreation, competitive, camps) and some leagues have criteria other than age to join a team. Also you will have to track the parent's activities to make sure they bring the snackie cakes, juice drinks, refreshments, awards and favors for the afterglow parties. It is best to level up for the complexity and generality this application will ultimately demand.
Dan, you seem to be very experieced in Big League development work for Little League, so you have probably done work on this area before. .
All kidding aside, I'm sure a full featured app for managing the full scope of team activities would be a very complex app!
Mark
You guys are funny. Here's is my age formula for my application. Because I deal in the healthcare industry, and people die, I don't want the age field to continue making them older.
Year([DOD])-Year([DOB])-1
+
If(Month([DOD])>Month([DOB]),1,0)
+
If(Month([DOD])=Month([DOB]) and Day([DOD])>=Day([DOB]),1,0)
I'm sure the OP could just replace DOD with a field set to 7/31
