# Calculate the number of overlapping months between three date ranges

#### NathanA

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

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

### 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
Can you please care to explain how the result is 4 in the first part in the picture.

Replies
7
Views
131
Replies
1
Views
175
Replies
1
Views
108
Replies
2
Views
63
Replies
18
Views
470

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.

### Which adblocker are you using?

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

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