ERROR check if a date set entered violates a previous date set

[buzz]

I am devloping an excel program that has a list of date sets, not
necessarily in sequencial order. What I want is an ERROR CHECK if a
date set violates any date set previously entered.
What I was looking for was that a discharge could not be typed in as
follows:
1/10/04 to 4/20/04
2/10/04 to 3/20/04
The second date falls within the first and should be invalid setting
off a message or an alert of an invalid entry. I am interested in
making it idiot proof so that one could not enter dates within or
covering dates already entered. This one really throws me, Please
help.

 

[Dave O]

The answer to your question is to check the "from" and "to" date of
the current entry to see if it falls between the "from" and "to" date
of the previous entry. If either date falls into the previous date
range, generate a message.

What makes this complicated is the way the data is entered: as it
stands,
1/10/04 to 4/20/04
2/10/04 to 3/20/04

… occupies just 2 cells and contains spaces and the word "to", which
means these are text cells. To do any meaningful work on these
entries, you'll need to cut them apart and convert them to dates.
This can be done, but it's chunky, particularly since you need to cut
it apart 8 times. Is it possible for you alter the sprdsht to enter
the dates in a "from" and a "to" column? If you can do that, you'll
simplify your formula.

If you can make this change, and assuming your layout looks like this
Col Col
A B
Row 1 1/10/04 4/20/04
Row 2 2/10/04 3/20/04

The formula in cell C2 is
=IF(AND(OR(A1>A2,A2>B1),OR(A1>B2,B2>B1)),"","Date overlap")

Contact me for further discussion: (e-mail address removed)