Formula to return duration

[mLife]

Hi,
Firstly, I’m an absolute novice on excel. However, I’m trying to get excel to return an accurate duration in years and months, based on a start date and end date. I found the formula below online:

=DATEDIF(A2,B2,"y")&" Years, "&DATEDIF(A2,B2,"ym")&" Months"

the issue with this formula is that it isn’t accurate when trying to establish a contract duration or length of service. It’s seems to miss a month off. For example, 1/1/19 to 31/03/2020 is calculated as 1 year, 2 months using that formula, but in real terms it’s 1 year and 3 months. It’s seems like the date has to enter in to the next month before a month registers.

Does anyone have a formula/workaround to combat this issue?

My apologies in advance if this has been posted previously.

 

[Michael M]

Try using
=DATEDIF(A2,B2,"y")&" Years, "&DATEDIF(A2,B2,"ym")+1&" Months"
As the end date is a completed month, it won't count that month

 

[Oaktree]

Depends on what you're trying to do, but you might also consider something like this where you round the months instead of specifically needing to elapse 100% of the month to count:
=INT((B1-A1)/365)&" Years " & ROUND(MOD((B1-A1)/365,1)*12,0)&" Months"

 

[mLife]

Try using
=DATEDIF(A2,B2,"y")&" Years, "&DATEDIF(A2,B2,"ym")+1&" Months"
As the end date is a completed month, it won't count that month

Thanks, I tried this, however when formula calculates 11 months, the +1 defaults month to 0 and it doesn’t add another year.

 

[mLife]

Depends on what you're trying to do, but you might also consider something like this where you round the months instead of specifically needing to elapse 100% of the month to count:
=INT((B1-A1)/365)&" Years " & ROUND(MOD((B1-A1)/365,1)*12,0)&" Months"

Perfect! I think you’ve solve the issue. Thank you so much. This provided quite a headache for the excel experts in my office. Thanks again.