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?
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.