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

 

[Qull666]

Following your requirements, this could work:

=IF(H4<DATE(2007,1,9),"",IF(I4<$A$2,"",IF(AND(J4<H4,K4>H4),"EX",IF(AND(ISBLANK(K4),J4<H4),"EX",""))))

HTH.

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.