# DATEDIF result in years and months - duplicate

#### HerbsG

##### New Member
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?

 Total​ 9.00​ 9.01​ 9.02​ …..​ 9.09​ 9.10​ 9.11​ 10.00​ 10.01​

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### jasonb75

##### Well-known Member
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

##### New Member
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

Replies
17
Views
259
Replies
1
Views
195
Replies
3
Views
92
Replies
1
Views
83
Replies
10
Views
172