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

#### dlsmith36

##### New Member
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

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### 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

##### New Member
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:
Code:
``=DATEDIF(MAX(O2,DATEVALUE("01-Jan-17")),MIN(P2,DATEVALUE("31-Dec-17")),"m")+1``

#### dlsmith36

##### New Member
Worked like a charm. Thank you so much!!!!

You are welcome!

#### dlsmith36

##### New Member
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

##### New Member
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