Hope someone can help, I'm looking for a formula to work out date overlaps.
e.g.
If my key dates are 1/11/08 to 30/11/08
I want to auto calculate how many days the following overlap with my key dates
01/01/08 to 31/12/08
16/11/08 to 05/12/08
etc

thanks!
--
Liz

=MAX(0,MIN(KeyDateEnd,$B1+1)-MAX(KeyDateStart,$A1))+1

--
__________________________________
HTH

Bob

"liztownsend" wrote in message
...
Hope someone can help, I'm looking for a formula to work out date
overlaps.
e.g.
If my key dates are 1/11/08 to 30/11/08
I want to auto calculate how many days the following overlap with my key
dates
01/01/08 to 31/12/08
16/11/08 to 05/12/08
etc

thanks!
--
Liz

Try
1/1/2008 20/11/2008
9/11/2008 11/11/2008

Herbert Seidenberg wrote:
Try
1/1/2008 20/11/2008
9/11/2008 11/11/2008

Ah, a misplaced paren:

=MAX(0,MIN(KeyDateEnd,$B1)+1-MAX(KeyDateStart,$A1))+1
^^^

smartin wrote:
Herbert Seidenberg wrote:
Try
1/1/2008 20/11/2008
9/11/2008 11/11/2008

Ah, a misplaced paren:

=MAX(0,MIN(KeyDateEnd,$B1)+1-MAX(KeyDateStart,$A1))+1
^^^

If anyone is still looking at this, I wonder if there is an array
solution that will do it? I think such a solution could have much wider
applications that touch on set operations.

E.g., In general, given two series of discreet values, denoted by their
respective endpoints,

series A: a...b
and
series B: c...d

1) Determine how many values are common to A and B, or
2) (better) Enumerate the values common to A and B so they can be
counted, summed, etc.
3) (going a step further) Enumerate the non-intersect values of A and B

Anyone up for a challenge? I know I am--and I dabbled at this, but my
grasp of handling arrays is feeble at best.

The problem is that
1/11/08 to 30/11/08
when fully expanded reads:
1/11/08 12:00 AM to 30/11/08 12:00 AM
What we really want is:
1/11/08 00:00 to 30/11/08 24:00
which shortens to:
1/11/08 to 1/12/08
If we correct all the end dates this way and
remove all the +1's in Bob's formula, everything works.
Ron's formula now fails and it has problems
if we include odd times.

Ron's is an array formula, but why use an array formula when you can do it
without.

--
__________________________________
HTH

Bob

"smartin" wrote in message
...
smartin wrote:
Herbert Seidenberg wrote:
Try
1/1/2008 20/11/2008
9/11/2008 11/11/2008

Ah, a misplaced paren:

=MAX(0,MIN(KeyDateEnd,$B1)+1-MAX(KeyDateStart,$A1))+1
^^^

If anyone is still looking at this, I wonder if there is an array solution
that will do it? I think such a solution could have much wider
applications that touch on set operations.

E.g., In general, given two series of discreet values, denoted by their
respective endpoints,

series A: a...b
and
series B: c...d

1) Determine how many values are common to A and B, or
2) (better) Enumerate the values common to A and B so they can be counted,
summed, etc.
3) (going a step further) Enumerate the non-intersect values of A and B

Anyone up for a challenge? I know I am--and I dabbled at this, but my
grasp of handling arrays is feeble at best.

I don't think so!

--
__________________________________
HTH

Bob

"smartin" wrote in message
...
Herbert Seidenberg wrote:
Try
1/1/2008 20/11/2008
9/11/2008 11/11/2008

Ah, a misplaced paren:

=MAX(0,MIN(KeyDateEnd,$B1)+1-MAX(KeyDateStart,$A1))+1
^^^

Bob Phillips wrote:
I don't think so!


Heh! I must have missed something. Seemed like it worked, but I did not
test using nonzero times in the dates.

On Thu, 11 Sep 2008 03:27:01 -0700, liztownsend
wrote:

Hope someone can help, I'm looking for a formula to work out date overlaps.
e.g.
If my key dates are 1/11/08 to 30/11/08
I want to auto calculate how many days the following overlap with my key dates
01/01/08 to 31/12/08
16/11/08 to 05/12/08
etc

thanks!


=SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(A5&":"&B5)),ROW(INDIRE CT(KeyDateStart&":"&KeyDateEnd)),0)))

Where your dates of interest are in A5 and B5
--ron