Calculate the number of overlapping months between three date ranges

[NathanA]

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):

=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):

=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.

 

[CA_Punit]

Can you please care to explain how the result is 4 in the first part in the picture.