# Excel Date Calculations

The Curzon Kid 21:44 06 Mar 2005
Locked

I have a query regarding calculating ages and using the result in the
VLOOKUP function.

I have 2 dates, both entered manually, one is a date of birth and the other
is a date of a cycle race. I then need to refer to a table of standard times
for given ages at specific distances. I have got the VLOOKUP function
working o.k. but I can't get the date calculation to work so that when the
DOB is earlier than the current date it gives the correct age in whole
years. The calculation seems only to take into account the year and not the
day and month.
My table of ages, distances and standard times have the ages in whole years.

I have used the formula =Year (A1)-YEAR(B1)-1.
I have also used the formula =YEARFRAC(C4,\$D\$1,1)

Can you make sense from the above or do you need my worksheet?

Many thanks,

stalion 21:47 06 Mar 2005

good evening to you

VoG II 21:49 06 Mar 2005

You might try using the undocumented function DATEDIF.

=DATEDIF(FirstDate,SecondDate,"Interval")
FirstDate : This is the earliest of the two dates.
SecondDate : This is the most recent of the two dates.

"Interval" : This indicates what you want to calculate.

These are the available intervals.
"d" Days between the two dates.
"m" Months between the two dates.
"y" Years between the two dates.
"yd" Days between the dates, as if the dates were in the same year.

"ym" Months between the dates, as if the dates were in the same year.

"md" Days between the two dates, as if the dates were in the same month and year.

VoG II 21:51 06 Mar 2005

Sorry, formatting went awry:

=DATEDIF(FirstDate,SecondDate,"Interval")

FirstDate : This is the earliest of the two dates.

SecondDate : This is the most recent of the two dates.

"Interval" : This indicates what you want to calculate.

These are the available intervals.

"d" Days between the two dates.

"m" Months between the two dates.

"y" Years between the two dates.

"yd" Days between the dates, as if the dates were in the same year.

"ym" Months between the dates, as if the dates were in the same year.

"md" Days between the two dates, as if the dates were in the same month and year.

VoG II 21:52 06 Mar 2005

Evening Sir. I knew the forum times were a bit iffy but how did you manage that ;o)

The Curzon Kid 21:53 06 Mar 2005

Thanks for this; I will give it a try and let you know how I get on.

The Curzon Kid 21:56 06 Mar 2005

The forum time is about 10 minutes in front of my PC clock!

The Curzon Kid 22:16 06 Mar 2005

Thanks; it looks like you have solved my query. It will now work as I need it to. Brilliant!

Eargasm 22:26 06 Mar 2005

Excel-lent advice from VoGâ„˘ as usual.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

Creative studio Omnibus' brand identity for We Said Enough, a non-profit against sexual misconduct

What to ask Siri on the HomePod

Meilleurs VPN (2018)