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.

click here

  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

Android Oreo News: Release Date & New Features

Best Photo Editing Apps for iPad

iMac 21.5-inch (2017) review

Comment regarder le combat entre McGregor et Mayweather ?