Forum Discussion

Dominica Smith's avatar
Dominica Smith
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's avatar
    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's avatar
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's avatar
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.

Resources