Excel Count Overlapping Days - Microsoft Community

i trying count number of overlapping days between multiple date ranges.  have date ranges in cells e4 thru af4 each  2 set ex:  start , finish in cell e4 , e5 respectively, next date range start g4 , h4 respectability, , on.

i need figure out either

option a) how count how many of days overlapping across entire row

or

option b) give me total number of days while excluding overlapping days.

above spreadsheet looks like. in column project name need figure out across duration of project how many days took not include overlapping days each step

enter below formula array formula (ctrl+shift+enter) in cell e2:

=sum(--(frequency(if(row(indirect (min ($e$4:$af$4)&":"&max ($e$4:$af$4)))<=if(mod( column($f$4:$af$4),2)=0, $f$4:$af$4,0), if(row(indirect (min( $e$4:$af$4)&":"&max( $e$4:$af$4)))>= if(mod( column( $e$4:$ae$4), 2)=1,$e$4:$ae$4,0), row( indirect(min( $e$4:$af$4)&":" &max( $e$4:$af$4))))), row( indirect(min( $e$4:$af$4)&":"&max( $e$4:$af$4))))>1))

this calculate number of duplicate dates between min date , max date in range e14:af14 - please note formula considers odd number column (ie. 5) first cell in range column e ie. column no 5.

regards,

amit tandon

www.globaliconnect.com

Office / Excel / Microsoft Office Programming / Office 2016