Help need formula for working out date overlaps

[liztownsend]

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!

 

[Bob Phillips]

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

 

[Herbert Seidenberg]

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

 

[smartin]

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]

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.

 

[Ron Rosenfeld]

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(INDIRECT(KeyDateStart&":"&KeyDateEnd)),0)))

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

 

[Herbert Seidenberg]

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.

 

[Bob Phillips]

I don't think so!

 

[Bob Phillips]

Not all of them, just one I think

=MAX(0,MIN(KeyDateEnd,$B2)-MAX(KeyDateStart,$A2))+1

Is Herbert Seidenberg and liztownsend one and the same?

 

[Bob Phillips]

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

 

[Ron Rosenfeld]

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.

Herbert,

My formula was not designed to handle anything other than full days, as
requested.

What are the circumstances under which it fails?
--ron

 

[Ron Rosenfeld]

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

Easier for a simple mind like mine to understand?
--ron

 

[smartin]

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.

 

[Bob Phillips]

I think your mind is far from simple Ron

 

[Ron Rosenfeld]

I think your mind is far from simple Ron

--

Thank you.

I guess what I'm really trying to say is that different people think in
different ways. For me, in this kind of problem, it's easy for me to think in
terms of arrays. And even if it is not the most efficient algorithm for
solving the problem, unless the database is very large, it will likely be "good
enough" for now.

Your solution will certainly run faster, and will also not run into the dreaded
"5 Jun 2079" problem in pre-2007 versions, as would my solution )
--ron

 

[Bob Phillips]

It was just a statement of fact by someone who reads your responses as a
matter of course because I know they will be enlightening. And my initial
comment wasn't meant as criticism of your formula, but more as extra
information for someone who joined in suggesting that an array formula might
do it, but didn't actually offer one.

As for 2079, I don't know about you, but I think it is beyond my realm of
needing to worry about <bg>