Comparing Dates in 3 different Columns

[Deepinconsternation]

Ok, we have a spreadsheet A -M with 100 rows.
Two date columns require to be compared and then produce a List.
If date column (a)is greater than date 01/01/2001 and date column (b) is not greater than or equal to date column (A)list by Column (C), (D) with A & B.

(C) is Text formatted cell column, D column is formatted to General.

Purpose: If something happened on date A but no action has resulted per date B then list those Names and references that correspond)

Now I HAVE busted my brains over this. No guru but surely it can be done. Functions do not appear to fit the problem
Thanks


_________________
DPConsternation

[ This Message was edited by: Deepinconsternation on 2002-04-27 21:11 ]

[Yogi Anand]

On 2002-04-27 21:08, Deepinconsternation wrote:
Ok, we have a spreadsheet A -M with 100 rows.
Two date columns require to be compared and then produce a List.
If date column (a)is greater than date 01/01/2001 and date column (b) is not greater than or equal to date column (A)list by Column (C), (D) with A & B.

(C) is Text formatted cell column, D column is formatted to General.

Purpose: If something happened on date A but no action has resulted per date B then list those Names and references that correspond)

Now I HAVE busted my brains over this. No guru but surely it can be done. Functions do not appear to fit the problem
Thanks


_________________
DPConsternation

[ This Message was edited by: Deepinconsternation on 2002-04-27 21:11 ]

Hi Deepinconsternation:
If I have understood you correctly, and say, your data begins in row2, so cells A2 and B2 are Date cells, then try the following formula ...

=IF(AND(A2>DATEVALUE("01/01/2001"),A2>B2),"then do whtever in (C) and (D)","what ever to do otherwise")

HTH

Please post back if it works for you ... otherwise explain a little further and let us take it from there!

[Aladin Akyurek]

On 2002-04-27 21:08, Deepinconsternation wrote:
Ok, we have a spreadsheet A -M with 100 rows.
Two date columns require to be compared and then produce a List.
If date column (a)is greater than date 01/01/2001 and date column (b) is not greater than or equal to date column (A)list by Column (C), (D) with A & B.

(C) is Text formatted cell column, D column is formatted to General.

Purpose: If something happened on date A but no action has resulted per date B then list those Names and references that correspond)

Now I HAVE busted my brains over this. No guru but surely it can be done. Functions do not appear to fit the problem
Thanks


_________________
DPConsternation

[ This Message was edited by: Deepinconsternation on 2002-04-27 21:11 ]

Not enough info for a secure assessment, but it looks like you could use Advanced Filter.

Lets say that A4:D8 houses the following sample data

{"Date1","Date2","Names","Refs";
36892,36883,"c1","d2";
36896,36883,"c2","d2";
36945,36944,"c3","d4";
36947,36947,"c4","d4"}

Numbers under date headings are real dates, printed here in Excel's internal encoding of dates, so don't worry about them.

For Advanced Filter to work, it's absolutely necessary that column headings Date1, Date2, etc. are distinctly formatted, e.g., in bold and italic.

In A2 enter:

=A5>"1-Jan-01"+0

In B2 enter:

=B5
Activate A5.
Activate Data|Filter|Advanced Filter.
Check Copy to another location.
Enter for List range: $A$4:$D$8
Enter for Criteria range: $A$1:$B$2
Enter for Copy to: $F$4
Click OK.

That's what you get to see in the results area from F4 on:

{"Date1","Date2","Names","Refs";
36896,36883,"c2","d2";
36945,36944,"c3","d4"}

Aladin