Number of days in overlapping date ranges (using array formula?)
Is brute forcing the result an option? In C10:
=IF(OR(($B10)<$A$1,$A10$B$1),0,(MIN(($B10),$B$1)-MAX($A10,$A$1)+1))*$C1+IF(OR(($B10)<$A$2,$A10$B$2 ),0,(MIN(($B10),$B$2)-MAX($A10,$A$2)+1))*$C$2+IF(OR(($B10)<$A$3,$A10$B$ 3),0,(MIN(($B10),$B$3)-MAX($A10,$A$3)+1))*$C$3
That will fill down so that we get 1800 at C11.
"ajnmx" wrote:
I have a date range in columns A and B then a number in column C:
05-Jan-09 09-Feb-09 100.00
10-Feb-09 25-Mar-09 200.00
26-Mar-09 11-Apr-09 300.00
Then I have the 'input' section (rows 10 and 11), a further date
range:
Date From Date To
07-Feb-09 13-Feb-09
23-Mar-09 29-Mar-09
What I want to do is calculate the number of days that the date ranges
overlap, and multiply it by the value in column C.
So for example, the date range 7-Feb to 13-Feb overlaps the range 5-
Jan to 9-Feb by three days, and overlaps the range 10-Feb to 25-Mar by
four days. So the answer I'm looking for is 3x100 plus 4x200 = 1100.
There are rows and rows of this stuff, so I'm looking for a formula
that I can just copy down. I've been playing around with array
formulas but I can't get anything to work.
I've used this formula which works but obviously only for one line:
=IF(OR(($B10)<$A$1,$A10$B$1),0,(MIN(($B10),$B$1)-MAX($A10,$A
$1)+1))*C1
I thought I could turn this unto an array function like this:
=IF(OR(($B10)<($A$1:$A$3),$A10($B$1:$B$3)),0,(MIN (($B10),($B$1:$B$3))-
MAX($A10,($A$1:$A$3))+1))*(C1:C3)
....but it doesn't seem to work
Can anyone help?
|