Forum Discussion

Copper Contributor

Oct 10, 2017

Solved

Excel Formula to calculate month between dates in different years

Hi Excel Community, I consider myself an advanced user of Excel but evern this formula has me a little stumped. I have a data sheet with various lines of contracts with varying contract term date...

excel

Formulas and Functions

Dominica Smith
Oct 10, 2017
Thanks for the quick response, however, how do we get it to simply count the months instead of full months?

Example: 1-Jan-17 to 28-Feb-17 equals 2, not 1.

Dominica Smith

Copper Contributor

Oct 10, 2017

Thanks for the quick response, however, how do we get it to simply count the months instead of full months?

Example: 1-Jan-17 to 28-Feb-17 equals 2, not 1.

Dominica Smith

Copper Contributor

Oct 10, 2017

Figured it out!!!
=DATEDIF(MAX(O2,DATEVALUE("01-Jan-17")),MIN(P2,DATEVALUE("31-Dec-17")),"m")+1

Benjamin Elias
MVP
Oct 11, 2017
I'd also suggest adding two columns and using those to split out the min and max parts of this function. Just to make it a little clearer to anyone else who needs to work with this formula down the track.
Bryant Boyer
Brass Contributor
Oct 10, 2017
Well done! Very clever :)