DATEDIF result in years and months - duplicate

HerbsG

New Member
Joined
Sep 5, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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 dateEnd dateResult
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,974
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Sep 5, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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 months9 Years 2 months9 Years 3 months9 Years 4 months


This now works - many thanks

 

Watch MrExcel Video

Forum statistics

Threads
1,112,942
Messages
5,543,126
Members
410,583
Latest member
jgalin
Top