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

dlsmith36

New Member
Joined
Oct 10, 2017
Messages
15
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
 

Some videos you may like

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,974
Office Version
365
Platform
Windows
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
Joined
Oct 10, 2017
Messages
15
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,974
Office Version
365
Platform
Windows
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
Joined
Oct 10, 2017
Messages
15
Worked like a charm. Thank you so much!!!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,974
Office Version
365
Platform
Windows
You are welcome!
 

dlsmith36

New Member
Joined
Oct 10, 2017
Messages
15
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,974
Office Version
365
Platform
Windows
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
Joined
Oct 10, 2017
Messages
15
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,974
Office Version
365
Platform
Windows
How many months would these two return?

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

Watch MrExcel Video

Forum statistics

Threads
1,095,369
Messages
5,444,058
Members
405,264
Latest member
JohnP1972

This Week's Hot Topics

Top