Excel - count days in each month between two dates both dates available

Question

I have found the solution thought it would be good to share:

so the formula should be : =((MAX(CT$6-$N8,0)-MAX(EOMONTH(CT$6,-1)-$N8,0))-(MAX(CT$6-$O8,0)-MAX(EOMONTH(CT$6,-1)-$O8,0))+(EOMONTH(CT$6,0)=EOMONTH($N8,0)))*1

:)

I am trying to write an excel formula which can be dragged across a row of cells to give the number of days in each month between two specified dates. E.g:

I tried using a if with sum product formula from the same website but for some reason it is not recognising the days of last month. was wondering if someone could help explain what this formula does and how to correct it.

=IFERROR(IF(AND(CS$6>=$M9,CS$6<=$N9),SUMPRODUCT(--(MONTH(ROW(INDIRECT($M9&":"&IF($N9="",TODAY(),$N9))))=MONTH(CS$6))),),"")

Answer 1

Anyway, here's an alternative completeness approach based on the conventional formula.

=max(0,min(end1,end2)-max(start1,start2)+1)

for the overlap of two date ranges, which results in

=MAX(0,MIN(IF($B2="",TODAY(),$B2),EOMONTH(DATEVALUE(C$1&"-17"),0))-MAX($A2,DATEVALUE(C$1&"-17"))+1)