Convert days into Years/Months/Days

[FryGirl]

I have two dates subtracted which gives yields 867

How to convert to ? Yrs ? Mths ? Days

 

[Rick Rothstein]

Thanks all. The Datedif worked well.

Did it? Try Ford's formula using 10/31/2016 as the start date and 3/1/2017 as the end date... is that what you want to see for an answer? Just out of curiosity, what would you like to see as the answer for those two dates?

The problem stems from the fact that using months as a time period in a difference between two dates is a terrible idea, mainly because there is no good definition of what a month's difference is. For example, is the difference between January 31st and February 28th (of a non leap year) one full month or only 28 days? What about when that range is included within a slightly larger range, such as December 31st of one year and March 31st of the next year (in a non leap year)? Is the January to February span to be counted as one month or as 28 days. As I said, using months as in a date difference calculation is a terrible idea. Using Years, weeks and days would be much better.

 

[footoo]

Using Years, weeks and days would be much better.

=DATEDIF(A1,B1,"y") & " years, " & INT(DATEDIF(A1,B1,"yd")/7) & " weeks, " & MOD(DATEDIF(A1,B1,"yd"),7) & " days"

 

[FryGirl]

Did it? Try Ford's formula using 10/31/2016 as the start date and 3/1/2017 as the end date... is that what you want to see for an answer? Just out of curiosity, what would you like to see as the answer for those two dates?

I would expect to see 4 months and 1 day which I found this and it seems to work better. I agree, the negative number is not good.

=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&B1-EDATE(A1,DATEDIF(A1,B1,"m"))&" days"

Of course it will only work for 2007 and up as it uses the EDATE.

 

[Rick Rothstein]

I would expect to see 4 months and 1 day which I found this and it seems to work better. I agree, the negative number is not good.

=IF(DATEDIF(A1,B1,"y")=0,"",DATEDIF(A1,B1,"y")&" years ")&IF(DATEDIF(A1,B1,"ym")=0,"",DATEDIF(A1,B1,"ym")&" months ")&B1-EDATE(A1,DATEDIF(A1,B1,"m"))&" days"

Of course it will only work for 2007 and up as it uses the EDATE.

EDATE existed in XL2003 as long as you added the Analysis ToolPak to it via the Add-Ins dialog box (found in Tools/Add-Ins on its Menu Bar).

I consider the following correct as long as you are using months as a measuring criteria, but I wanted to make sure you realized it as well. Consider the following start and stop dates and what your formula calculates for them... notice that all of the red date differences are the same even though the number of days between their start and end dates are different... and the same is true for the blue highlighted dates as well. Of course these kinds of duplications can occur with other date ranges when moving across a month with less days in it than the month before... the date ranges below represent the extreme that is possible.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]12/27/2016[/TD]
[TD] 3/1/2017[/TD]
[TD]2 months 2 days[/TD]
[/TR]
[TR]
[TD]12/28/2016[/TD]
[TD] 3/1/2017[/TD]
[TD]2 months 1 days[/TD]
[/TR]
[TR]
[TD]12/29/2016[/TD]
[TD] 3/1/2017[/TD]
[TD]2 months 1 days[/TD]
[/TR]
[TR]
[TD]12/30/2016[/TD]
[TD] 3/1/2017[/TD]
[TD]2 months 1 days[/TD]
[/TR]
[TR]
[TD]12/31/2016[/TD]
[TD]3/1/2017[/TD]
[TD]2 months 1 days[/TD]
[/TR]
[TR]
[TD]1/1/2017[/TD]
[TD] 3/1/2017[/TD]
[TD]2 months 0 days[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1/27/2016[/TD]
[TD] 3/1/2017[/TD]
[TD]1 months 2 days[/TD]
[/TR]
[TR]
[TD]1/28/2016[/TD]
[TD] 3/1/2017[/TD]
[TD]1 months 1 days[/TD]
[/TR]
[TR]
[TD]1/29/2016[/TD]
[TD] 3/1/2017[/TD]
[TD]1 months 1 days[/TD]
[/TR]
[TR]
[TD]1/30/2016[/TD]
[TD] 3/1/2017[/TD]
[TD]1 months 1 days[/TD]
[/TR]
[TR]
[TD]1/31/2016[/TD]
[TD]3/1/2017[/TD]
[TD]1 months 1 days[/TD]
[/TR]
[TR]
[TD]2/1/2017[/TD]
[TD] 3/1/2017[/TD]
[TD]1 months 0 days[/TD]
[/TR]
</tbody>[/TABLE]

 

[FryGirl]

And ultimately I am using months as the indicator, so this is great information. Thanks for your time and explanation Rick.