Formula to calculate month between dates in different years

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.

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.

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

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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Latchmaker

Active Member
Joined
Sep 30, 2005
Messages
308
=SUM(DATEDIF(MAX(O2,DATEVALUE("01-Jan-17")),MIN(P2,DATEVALUE("31-Dec-17")),"m")+(IF(EOMONTH(P2,0)=P2,1,0)))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top