Convert days into Years/Months/Days

[FryGirl]

I have two dates subtracted which gives yields 867

How to convert to ? Yrs ? Mths ? Days

 

[Anthony47]

I have two dates subtracted which gives yields 867

How to convert to ? Yrs ? Mths ? Days

You need to use the function DATEDIF; you get examples on how using that function in this Microsoft article:
https://support.office.com/en-us/ar...wo-dates-8235e7c9-b430-44ca-9425-46100a162f38

Bye

 

[FDibbins]

Something like this...
=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&DATEDIF(A1,B1,"md")&" days"

 

[AhoyNC]

Another option would be to subtract the dates and use custom formatting.

Format as

[TABLE="width: 273"]
<colgroup><col width="273"></colgroup>[TR]
[TD="width: 273"]yy "years" mm "months" dd "days"[/TD]
[/TR]
[/TABLE]

Excel Workbook
	A	B	C
1	5/17/2015	9/30/2017	02 years 05 months 16 days
Sheet

 

[FDibbins]

Another option would be to subtract the dates and use custom formatting.

Format as

[TABLE="width: 273"]
<colgroup><col width="273"></colgroup>[TR]
[TD="width: 273"]yy "years" mm "months" dd "days"[/TD]
[/TR]
[/TABLE]

Excel Workbook
	A	B	C
1	5/17/2015	9/30/2017	02 years 05 months 16 days
Sheet

I love that, nice 1!!!

 

[Rick Rothstein]

Another option would be to subtract the dates and use custom formatting.

Format as

[TABLE="width: 273"]
<tbody>[TR]
[TD="width: 273"]yy "years" mm "months" dd "days"[/TD]
[/TR]
</tbody>[/TABLE]

Which, of course, means you can do it easily by formula as well if desired...

=TEXT(B1-A1,"yy"" years ""mm"" months ""dd"" days""")

 

[footoo]

Another option would be to subtract the dates and use custom formatting.

Format as

[TABLE="width: 273"]
<tbody>[TR]
[TD="width: 273"]yy "years" mm "months" dd "days"[/TD]
[/TR]
</tbody>[/TABLE]

	A	B	C

<colgroup><col style="width:30px; "><col style="width:76px;"><col style="width:76px;"><col style="width:203px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]5/17/2015[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]02 years 05 months 16 days[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C1 =B1-A1 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

This does not return the correct result because it returns the date (year/month/day) calculated from 1 Jan 1900 plus 867 days.
It returns 2 (year 1902,), 5 (May), 16 (day).

The correct answer should be 2 years, 4 months, 13 days

 

[FDibbins]

This does not return the correct result because it returns the date (year/month/day) calculated from 1 Jan 1900 plus 867 days.
It returns 2 (year 1902,), 5 (May), 16 (day).

The correct answer should be 2 years, 4 months, 13 days

Darn, I should have checked better
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

5/17/2015​

[/td][td]

9/30/2017​

[/td][td]

02 years 05 months 16 days​

[/td][td]2 years 4 months 13 days[/td][/tr]

[tr][td]

2​

[/td][td][/td][td][/td][td]Formatted[/td][td]calc'd[/td][/tr]
[/table]

That also means Rick's suggestion doesn't work, either

 

[AhoyNC]

footoo - Good catch. Like Ford I should have done the math.

 

[FryGirl]

Thanks all. The Datedif worked well.