Formula to calculate length of the contract using Begin date and end date

[Myexcel7]

Hello everyone,

Need help with the formula to calculate total length of the contract in Years and months

Effective date	End date	Length of contract
1/1/2024	11/30/2026	2 Yrs and 11 months
1/1/2024	12/31/2026	3 Yrs and 0 months

I tried using =DATEDIF(F6,G6,"Y")&" Yrs" &DATEDIF(F6,G6, "YM") &"M" but it gives me below for the example above. It shows one less month.

2 Yrs10M

2 Yrs11M

It gives me difference and deducts a month. Any help would be appreciated. Thanks.
SR

 

[Micron]

If this is similar to how dates are handled in Access, then November 30 is not considered to be completed until midnight of Dec 01, so you are one day short of a full month for November. Increase your end date to one second after midnight of Dec 01 (12/1/2026 12:00:01 AM) and you'll see what I mean. How to fix the result depends on what you want to see. One method could be to do math on the month portions, or perhaps change YM to D and do math on the days. That might depend on whether or not you want to see fractions of a month, or just return the integer portion(s).

 

[Myexcel7]

Thank you Micron. I would like to see-

Begin	End	I would like to see the cell display below in bold (Yrs and months)
1/1/2024	11/30/2026	2 Yrs and 11 months
1/1/2024	12/31/2026	3 Yrs and 0 months

Is there any formula that calculates the addition of 1 sec and to the end date and eventually give me my results in bold? Thank you.

 

[Micron]

I don't know if you reliably can or not if using formulas. Depends on your data and how you want to modify results. F'rinstance what if you're 2 days short of the end of a month? In your posted case, you're 1 day short of a full month when doing the math, regardless of anything else such as basing this on worked days or whatever. You can get results like 2 years, 10 months and 29.5 days (the fractional part will depend on whether or not you use time values in your dates).
For that, see "Calculating Age..."

You might also consider using =DAY(EOMONTH(G2,0)) where G2 is your end date. That will give you the number of days in a month instead. If you want a calculation to always be taken to the end of a month regardless, that might work for you.

 

[Myexcel7]

Hello Micron...I was able to solve it based on your suggestions. Appreciate your help!

 

[Micron]

Glad I could help. It's customary to post your solution in case it helps others in the future. You can mark your own post as the solution too.