Formula to calculate month between dates in different years - Three scenarios

[dlsmith36]

Hi Excel Community,

I consider myself an advanced user of Excel but even this formula has me a little stumped. I have a data sheet with various lines of contracts with varying contract term dates (start and end). Three different scenarios - start in 2016 and end in 2017, start in 2017 and end in 2017, start in 2017 and end in 2018.

I need to calculate the number of 2017 months in all scenarios. I think my formula needs to be like this:
If start date year does not equal 2017 and end date year equals 2017 then calculate # of months end date has in 2017.
Otherwise, if start date year does equal 2017 and end date equals 2018 then calculate # of months start date has left in 2017.

Here is the formula I started building but it is not giving me what I am looking for:
=(YEAR(P2)-YEAR("1/1/17"))*12+MONTH(P2)-MONTH("1/1/17")

Here is a screeshot sample of data:

Any ideas or help will be greatly appreciated.

Thanks,
Dominica Smith

 

[dlsmith36]

These scenarios would return the following.

11/28/17 - 12/2/17 = 1 month
10/2/17 - 11/29/17 = 2 months

 

[Joe4]

I am still not quite clear on your logic for determining when to count partial months or not, but see if this works for you:

=MAX(ROUND((MIN(P2,DATEVALUE("31-Dec-17"))-MAX(O2,DATEVALUE("01-Jan-17")))/(365/12),0),1)

If there are any records it does not work for, please post the example and explain why (explain the logic behind that determination).