Calculate age based on birthdate & end date

[Rachelle W.]

I've been looking through past posts on the subject, and am still confused.
I am building a database of race participants which requires the age of each
racer ON race day (July 4th, 2007). There's got to be a way to do it using
the race day and the birthdates, but I can't quite figure it out - and it's
got to be exact, due to the age divisions - if a racer is 55 ON race day and
56 the day AFTER, he or she needs to show up as being 55 in the database.
I've got 7/4/07 loaded in cell J1, and birthdates starting in J3. Any ideas?

 

[Mike H]

Try:

=DATEDIF(A1,A2,"y") & " y, " & DATEDIF(A1,A2,"ym") & " m, " &
DATEDIF(A1,A2,"md") & " d"

Birthday in A1
Race day in A2

Mike

 

[Sandy Mann]

Try:

=DATEDIF(J3,J1,"y")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mike H]

Sorry I missed the bit about how your data are laid out. Put this in K3 and
drag down to give the ages of the people in J3 down

=DATEDIF(J3,J$1,"y") & " y, " & DATEDIF(J3,J$1,"ym") & " m, " &
DATEDIF(J3,J$1,"md") & " d"

Mike

 

[Rachelle W.]

Perfect! I've never used DATEDIF before, and all the extra stuff about
months and days was confusing me. It works now.
Thanks!
Rachelle

 

[Rachelle W.]

Between you and Sandy, I got it - didn't need the month/day calculations
returned (just the age in years), so all that "extra" stuff in the formula
was confusing me.
Thanks!
Rachelle

 

[Sandy Mann]

You're very welcome. Mike picked up on the part that I missed. If you want
to copy the formula down using the fill handle make the J1 reference
absolute:

=DATEDIF(J3,$J$1,"y")

DATEDIF() is only documented in XL2000 but has been in all versions since
XL95
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rachelle W.]

*grin* Refreshed my memory regarding absolute references last week - but
thanks for the reminder. I originally created the spreadsheet in Open
Office, but the formula didn't "translate" to Excel, and there was,
apparently, quite the bruhaha at the first packet pick-up session the other
night. 'Tis fixed now, though, so we're aces.
Rachelle