Different Between Two Dates Returning Number of Years, Months, Days

[gymwrecker]

Need help with the following:

I have the following two dates.

A1=20090930
B1=20200531

I need the formula to return the following in C1: "XX Years, XX Months, XX Days".

Note: I searched the forum and couldn't find a formula for this.

 

[davesexcel]

Hello, is what you have in A1 & B1 recognized as dates?

 

[gymwrecker]

Hello, is what you have in A1 & B1 recognized as dates?

Yes. Thank you.

 

[Herakles]

Does this give you what you need?

=DATEDIF(DATE(LEFT($A$1,4),MID($A$1,5,2),RIGHT($A$1,2)),DATE(LEFT($B$1,4),MID($B$1,5,2),RIGHT($B$1,2)),"y") & " Years, " & MOD(DATEDIF(DATE(LEFT($A$1,4),MID($A$1,5,2),RIGHT($A$1,2)),DATE(LEFT($B$1,4),MID($B$1,5,2),RIGHT($B$1,2)),"m"),12) & " Months, " & MOD(DATEDIF(DATE(LEFT($A$1,4),MID($A$1,5,2),RIGHT($A$1,2)),DATE(LEFT($B$1,4),MID($B$1,5,2),RIGHT($B$1,2)),"d"),365) & " Days"

 

[gymwrecker]

I copied and pasted the formula; everything seems to be right but the years, it's returning the following:

389 Years, 7 Months, 335 Days

 

[davesexcel]

My result is different. BTW- what you have in A1 & B1 are not dates, but text? or do you have it formated that way?

 

[Herakles]

What have you got in A1 and B1?

 

[ExceLoki]

similar to what @Herakles posted
----------------

Book1
	A	B	C
1	20090930	20200531	10 Years, 8 Months, 246 Days
Sheet1

Cell Formulas
Range	Formula
C1	C1	=LET(a,DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),b,DATE(LEFT(B1,4),MID(B1,5,2),RIGHT(B1,2)),(DATEDIF(a,b,"y")&" Years, "&MOD(DATEDIF(a,b,"m"),12)&" Months, "&MOD(DATEDIF(a,b,"d"),365)&" Days"))

 

[Fluff]

How about

=DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"ym")&" Months "&DATEDIF(A1,B1,"md")&" Days"

 

[ExceLoki]

after looking at @Fluff 's post, think he's closer to the right formula, but it's not working for me as a1/b1 aren't date formats. so combining efforts i came up with:
-----------------

multiple posts solutions.xlsx
	A	B	C
1	20090930	20200531	10 Years 8 Months 1 Days
difference between dates

Cell Formulas
Range	Formula
C1	C1	=LET(a,DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),b,DATE(LEFT(B1,4),MID(B1,5,2),RIGHT(B1,2)),(DATEDIF(a,b,"Y")&" Years "&DATEDIF(a,b,"ym")&" Months "&DATEDIF(a,b,"md")&" Days"))