Number of days in overlapping date ranges (using array formula?)

[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?

 

[Wigi]

Did you check with Chip Pearson's formulas:

http://www.cpearson.com/excel/DateIntervals.htm

and other similar pages on his site.

 

[JLatham]

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.

 

[Ron Rosenfeld]

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?

I assumed your date ranges were in A1:B3.

Depending on how many date ranges you have, something like this should work:

=SUMPRODUCT(ISNUMBER(MATCH(ROW(INDIRECT(A10&":"&B10)),ROW(INDIRECT($A$1&":"&$B$1)),0))*$C$1)+
SUMPRODUCT(ISNUMBER(MATCH(ROW(INDIRECT(A10&":"&B10)),ROW(INDIRECT($A$2&":"&$B$2)),0))*$C$2)+
SUMPRODUCT(ISNUMBER(MATCH(ROW(INDIRECT(A10&":"&B10)),ROW(INDIRECT($A$3&":"&$B$3)),0))*$C$3)

There are some constraints on the allowable length of formula contents. In
Excel 2007, it is 8,192 characters. It might be less in earlier versions. But
if you run into that limitation, merely divide the formula into more than one
cell.
--ron

 

[JLatham]

The limit in 2003 is 1024 characters in a formula, shouldn't be any problem
with the length of the formula you provided. I was thinking of a
SUMPRODUCT() formula myself, but I couldn't get it worked out in my head this
evening, so I went with the brute force attack.

 

[Ron Rosenfeld]

The limit in 2003 is 1024 characters in a formula, shouldn't be any problem
with the length of the formula you provided. I was thinking of a
SUMPRODUCT() formula myself, but I couldn't get it worked out in my head this
evening, so I went with the brute force attack.

I thought it was something like that.

I wasn't concerned about the length of the formula I supplied -- only about
what would happen if he needed to extend it to test his input against more date
ranges than the three he shows.

Each SUMPRODUCT line is about 92 characters, so, in Excel 2003, he could get
about 11 date ranges to test against without having to go to a second cell.
--ron

 

[JLatham]

That's always a good thing to think of. Another thing, especially if lots of
dates or comparisons are going to take place, is speed. SUMPRODUCT() is a
pretty slow operation, and so the formula I put up earlier might offer better
performance IF there are lots of comparisons to be made. But for relatively
few comparisons, you would probably be hard pressed to measure any difference
in how long it took to get the results.
I personally prefer the SUMPRODUCT() solution over the brute force one I put
up simply because it is easier to understand and maintain. Most people get
confused to some degree when dealing with lots of logic statements (OR/AND)
in formulas, so that makes them more difficult to manage and maintain in the
long run.

 

[Ron Rosenfeld]

That's always a good thing to think of. Another thing, especially if lots of
dates or comparisons are going to take place, is speed. SUMPRODUCT() is a
pretty slow operation, and so the formula I put up earlier might offer better
performance IF there are lots of comparisons to be made. But for relatively
few comparisons, you would probably be hard pressed to measure any difference
in how long it took to get the results.
I personally prefer the SUMPRODUCT() solution over the brute force one I put
up simply because it is easier to understand and maintain. Most people get
confused to some degree when dealing with lots of logic statements (OR/AND)
in formulas, so that makes them more difficult to manage and maintain in the
long run.

No question that understandability and ease of maintenance are important
considerations. I sure have stuff I've done that works fine, but, in trying to
change/extend it, it takes me quite a while to figure out what I did!
--ron