Excel Date Calculations

The Curzon Kid 21:44 06 Mar 05
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 05

good evening to you

VoG II 21:49 06 Mar 05

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 05

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 05

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 05

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

The Curzon Kid 21:56 06 Mar 05

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

The Curzon Kid 22:16 06 Mar 05

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

Eargasm 22:26 06 Mar 05

Excel-lent advice from VoG™ as usual.

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

LG G6 review: Hands-on with LG’s bold, big-screen shot at perfection

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…