Comparing Birthdate to specific date in the future

[Guest]

I an building a worksheet for baseball registration. I need to compare the
Birthdate to the date we use to determine playing age in a division. I have
attempted to use the =datedif(date1,date2(), "interval") where
date1=birthdate, date2=playing age date, Interval="y". The result is #ref!
error.

For example: We have said the players age on 05/01/2006 is the playing age
for the league. I want to compare the birthdate to 05/01/2005, and get an age
value.

I would like to be able to update it yearly so that next year I can siply
change the 05/01/2006 date to 05/01/2007 and recalculate the age value.

Is this possible?

Am I going about this the wrong way?

What am I doing wrong?

 

[Bernie Deitrick]

With the birthdate in cell A1:

For May 1, 2006

=DATEDIF(A1,DATE(YEAR(NOW()),5,1),"y")

or this, for May 1, 2005

=DATEDIF(A1,DATE(YEAR(NOW())-1,5,1),"y")

HTH,
Bernie
MS Excel MVP

 

[Guest]

Wow That was fast. And it worked as well.

One other thing...
When I try to copy that the remaining column it copys the formula exactly.
How do I copy it to the remaining column so that the foruls compares the
birthdates for each player. There are close to 1000 players so doing it
manually wont work.

 

[Ron Rosenfeld]

I an building a worksheet for baseball registration. I need to compare the
Birthdate to the date we use to determine playing age in a division. I have
attempted to use the =datedif(date1,date2(), "interval") where
date1=birthdate, date2=playing age date, Interval="y". The result is #ref!
error.

For example: We have said the players age on 05/01/2006 is the playing age
for the league. I want to compare the birthdate to 05/01/2005, and get an age
value.

I would like to be able to update it yearly so that next year I can siply
change the 05/01/2006 date to 05/01/2007 and recalculate the age value.

Is this possible?

Am I going about this the wrong way?

What am I doing wrong?

You've got an extra set of parentheses in your formula after date2.

If the year of interest is in A1, and the players DOB is in A2, then the
player's age on the date of interest will be:

=DATEDIF(A2,DATE(A1,5,1),"y")




--ron

 

[Bernie Deitrick]

Copy the cell with the formula, and paste it down the column. The A1 will increment when you do
that.... Make sure that you don'e have $'s in your cell reference: A1 and not $A$1, for example.

HTH,
Bernie
MS Excel MVP