A
ajnmx
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?
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?