Comparing Dates in 4 Columns - Nested Formulas?

[foofoo]

I am working on a project that involves comparing dates in 4 different
columns. I would like to write a formula that shows the results of
comparing the columns. I suspect a nested formula will get me the
results I need - but I am not quite sure how to do this.

I need to write the formula in cell L4. The formula needs to compare
the following columns or cells:

Today's Date - Cell A2
Column H - CL Date
Column I - DT Date
Column J - CR Date
Column K - TC Date

The formula in cell L4 needs to account for the following conditions:

- If the date in Column H is earlier than 10/01/07, then put a blank
in Cell L4. Otherwise, go to next condition
- If the date in Column I is earlier than today's date in Cell A2,
then put a blank in Cell L4. Otherwise, go to next condition.
- If today's date in Column J is earlier than the date in Column H
AND if the date in Column K is later than the date in Column H, then
put "EX" in Cell L4. Otherwise, go to the next condition.
- If Column K is blank AND if the date in Column J is earlier than
the date in Column H, then put "EX" in Cell L4.

If no conditions are met, then leave Cell L4 Blank.Examples of what I
am looking for are shown below:

EXAMPLE 1
Cell A2=10/10/07
H4 I4 J4 K4 L4
09/28/07 10/31/07 08/25/07 09/27/07 Leave Blank

EXAMPLE 2
Cell A2=10/10/07
H4 I4 J4 K4 L4
09/15/07 10/09/07 08/25/07 08/08/07 Leave Blank

EXAMPLE 3
Cell A2=10/10/07
H4 I4 J4 K4 L4
10/08/07 11/01/07 10/05/07 10/11/07 EX

EXAMPLE 4
Cell A2=10/10/07
H4 I4 J4 K4 L4
10/08/07 11/01/07 10/05/07 EX


I am not comfortable working woth programs and would prefer to tackle
this problem with a formula.


Thanks!


Sandi

 

[T. Valko]

This does *exactly what you asked for* however, I don't think it's very
robust but then again I don't know how you intend to use this. For example,
will all or some cells not have entries and in what combinations? One of
your examples demonstrates that K4 can be empty but what about any of the
other cells? As is the formula's not too bad but if you have to test that
combinations of cells actually have dates in them then it will grow longer
and more convoluted. Also, some conditions are unaccounted for. For example,
if H4 >=10/1/2007, if I4 >=A2. Since these were undefined they result in a
blank cell.

=IF(H4<DATE(2007,10,1),"",IF(I4<A2,"",IF(OR(AND(J4<H4,K4>H4),AND(K4="",J4<H4)),"EX","")))

 

[Guest]

Dear Biff,

I didn't see this thread. If I did, would not keyboarded and moused the
other thread.

Sincerely.