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

 

[Joe4]

Welcome to the Board!

Are you looking for complete months only or partial months?
It would be helpful if you let us know your expected outcome for your three examples. That will indicate how you are counting those.

 

[dlsmith36]

I figured it out slightly, however, I need it to calculate based on the month, excluding days. Example: 1-Jan-17 to 28-Feb-17 equals 2, not 1.

Here is the formula I used below.

=IF(AND(YEAR(O2)=2017,YEAR(P2)=2017),
DATEDIF(O2-1,P2+1,"m"),


IF(AND(YEAR(O2)<>2017,YEAR(P2)=2017),
DATEDIF(DATE(2017,1,1)-1,P2,"m"),


IF(AND(YEAR(O2)=2017,YEAR(P2)<>2017),
DATEDIF(,DATE(2017,12,31)+1,"m"),


IF(AND(YEAR(O2)<>2017,YEAR(P2)<>2017),
0,


))))


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">Example: 1-Jan-17 to 28-Feb-17 equals 2, not 1.</body>

 

[Joe4]

I think this formula should do it all:

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

 

[dlsmith36]

Worked like a charm. Thank you so much!!!!

 

[Joe4]

You are welcome!

 

[dlsmith36]

Joe4 - using the formula, I realized that I missed a scenario as it worked for every other date scenario provided. However, when the dates start in the middle of the month like 8/15/17 - 11/15/17 the formula is resulting in 4 months instead of 3 months.

Removing the +1 will correct the formula for this scenario, however, make it incorrect for other scenarios like start date 11/16/16, end date 1/31/17 only counting 2017 months, resulting in 0 months.

O2 - start date
P2 - end date

Any ideas are greatly appreciated.

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

 

[Joe4]

However, when the dates start in the middle of the month like 8/15/17 - 11/15/17 the formula is resulting in 4 months instead of 3 months.

Please define the logic. When should it count partial months and when shouldn't it?

 

[dlsmith36]

joe4 - Thanks for replying again. Here is the formula examples for full and partial months


Full months are counted when the start date (O2) begins on day of month and end date (P2) ends on last day of month.


Examples:
1/1/17 - 12/31/17 = 12 months
2/1/17 - 2/28/17 = 1 month
8/1/17 - 11/31/17 = 4 months
2/1/17 - 4/30/2017 = 3 months


Partial months are counted when the start date (O2) falls after the first of day of the month and end date (P2) ends on any date other than the last day of month.


Examples:
1/3/17 - 4/3/17 = 3 months
8/15/17 - 11/15/17 = 3 months
3/15/17 - 7/1517 = 3 months
11/28/17 - 12/20/17 = 1 month

 

[Joe4]

How many months would these two return?

11/28/17 - 12/2/17
10/2/17 - 11/29/17