How can you use Excel to determine the number of days occurring between several date ranges? -

How can you use Excel to determine the number of days occurring between several date ranges? -

hey hoping can help me out, first time posting stuck trying write formula in excel record breaks , week in occurred. have list of week ending dates, time time break (or breaks) may occur, want able record break range (say 8/11/2014-22/11/2014) , have formula provide value next each week effected break showing how many days in period affected.

eg.

week|weekend|break

week 1 10/08/2014 ?

week 2 17/08/2014 ?

week 3 11/08/2014 ?

week 4 18/08/2014 ?

breaks

break 1 11/08/2014-13/08/2014

break 2 17/08/2014-18/08/2014

hope makes sense, have tried write if formula maintain going cross eyed trying ranges right , having not count above 7 days in week. think may approaching exclusively wrong way.

thanks in advance!

try following. not scale -- if have lot of breaks vba macro may way go (it might possible arrays, suspect painful).

the formula in d2 should c2, except references row 11 replaced references row 12. in e2, utilize row 13, etc. having set formulas in c2->y2 (or many need) should able re-create row 2 down.

| b | c | d | ... | z 1 week | weekend | break1 | break2 | ... | days out 2 week 1 | 10/8/14 | = if(and($b$11>($b2-7), | = if(and...| ... | =sum(c2:y2) $b$11<=$b2), if($b2<$c$11,$b2-$b$11+1,$c$11-$b$11+1), if(and($b2-$c$11<7,$b2>=$c$11),$c$11-($b2-6)+1,0)) 3 week 2 | 17/8/14 | = if(and($b$11>($b3-7), ... 4 ... .... 10 break | start | end 11 break 1| 11/8/14 | 13/8/14 12 break 2| 17/8/14 | 18/8/14 13 ...

the formula in c2:

checks see if break starts in week ending on weekend if does, checks see if ends in week well. if so, days out days in break (break end - break start + 1). if not so, days out days start of break weekend (weekend - break start + 1) if break not start in week ending on weekend, check see if break ends in week. if so, days out (break end - week start + 1). otherwise, there no days out.

excel excel-formula