Calculate the number of overlapping months between three date ranges

NathanA

New Member
Joined
Jan 18, 2017
Messages
34
Hello everybody,

I am using Excel to calculate the number of overlapping month ends between three date ranges for the current year (2020) and next year (2021). Currently, I have two formulas to do this with two date ranges, however, I'm unsure how to extend it to work with three date ranges. I have been using Excel formulas, however I am open to switching to VBA if it is necessary.

Current year (given that the start dates are in column B and the end dates in column C, and the first date range is in row 4 and the second in row 5):
Code:
=IFERROR(
    IF(OR(YEAR($B4)=YEAR(TODAY()),YEAR($B5)=YEAR(TODAY())),
        DATEDIF(EOMONTH(MAX($B4,$B5),-1)+1,MIN($C4,$C5,DATE(YEAR(TODAY())+1,1,1)),"m"),
    IF(OR(YEAR($C4)=YEAR(TODAY()),YEAR($C5)=YEAR(TODAY())),
        DATEDIF(DATE(YEAR(TODAY())-1,12,31),MIN($C4,$C5),"m"),
    IF(AND(YEAR($B4)<YEAR(TODAY()),YEAR($B5)<YEAR(TODAY()),YEAR($C4)>YEAR(TODAY()),YEAR($C5)>YEAR(TODAY())),
        12))),
0)

Next year (given that the start dates are in column B and the end dates in column C, and the first date range is in row 4 and the second in row 5):
Code:
=IFERROR(
    IF(OR(YEAR($B4)=YEAR(TODAY())+1,YEAR($B5)=YEAR(TODAY())+1),
        DATEDIF(EOMONTH(MAX($B4,$B5),-1)+1,MIN($C4,$C5,DATE(YEAR(TODAY())+2,1,1)),"m"),
    IF(OR(YEAR($C4)=YEAR(TODAY())+1,YEAR($C5)=YEAR(TODAY())+1),
        DATEDIF(DATE(YEAR(TODAY()),12,31),MIN($C4,$C5),"m"),
    IF(AND(YEAR($B4)<YEAR(TODAY())+1,YEAR($B5)<YEAR(TODAY())+1,YEAR($C4)>YEAR(TODAY())+1,YEAR($C5)>YEAR(TODAY())+1),
        12))),
0)

I have included some examples below which show what I would expect based on given date ranges.

1594942120721.png


I would appreciate any help. Thank you for looking at this.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
893
Office Version
  1. 365
Platform
  1. Windows
Can you please care to explain how the result is 4 in the first part in the picture.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,827
Members
415,859
Latest member
Vain

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