DATEDIF result in years and months - duplicate

[HerbsG]

I need to create a mark book.

=VLOOKUP(G3,'Yr5 Maths Aut'!$A$1:$U$112,MATCH(I3,'Yr5 Maths Aut'!$A$1:$U$1,0),0)

• The Vlookup table uses the following Ages 9 yrs 1 month, 9 yrs 2 months etc to 9 yrs 10 months.

Total​

9.00​

9.10​

9.20​

…..​

9.90​

9.10​

9.11​

10.00​

10.1​

The problem is: =DATEDIF(D7,$E$1,"Y")&"."&DATEDIF(D7,$E$1,"YM")

When using the formula to compare Date of Birth to current date (today's date), the value returns 9.1.

e.g - I get two 9.1 results!

Start date	End date	Result
19/06/2011​	05/09/2020​	9.2
19/07/2011​	05/09/2020​	9.1
19/10/2010​	05/09/2020​	9.10

Is there a way to return 9.01 for 9 years 1 month?

Then read the table

Total​

9.00​

9.01​

9.02​

…..​

9.09​

9.10​

9.11​

10.00​

10.01​

 

[jasonb75]

Try

=DATEDIF(D7,$E$1,"Y")&"."&TEXT(DATEDIF(D7,$E$1,"YM"),"00")

You might need to use G3+0 in the lookup formula depending on how the lookup table is formatted.

 

[HerbsG]

Try

=DATEDIF(D7,$E$1,"Y")&"."&TEXT(DATEDIF(D7,$E$1,"YM"),"00")

You might need to use G3+0 in the lookup formula depending on how the lookup table is formatted.

Many thanks..
I have tried =DATEDIF(D3,$E$1,"Y")&" Years "&DATEDIF(D3,$E$1,"YM") &" months"
returns the value

9 Years 2 months

The vlook up reference table is now

9 Years 1 months

9 Years 2 months

9 Years 3 months

9 Years 4 months

This now works - many thanks