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

Add Bullets to Range
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
Joined
Dec 30, 2008
Messages
11,015
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,114,253
Messages
5,546,785
Members
410,757
Latest member
jonni
Top