less than another value

[Bree]

I am trying to find out if there is a way to find any dates less than a
specific date (1/1/2002), then replace all with text. Any suggestions?

 

[Max]

Maybe something along these lines ..

Assuming the dates are in A2 down

Put the specific date in B1: 1-Jan-2002 (say)

Put in B2: =IF(A2<=$B$1,TEXT(A2,"dd-mmm-yy"),A2)
Copy B2 down

Freeze the results in B2 down with an in-place:
copy > paste special > values >ok

 

[CLR]

Click on your list, then Data > Filter > AutoFilter..............select the
arrow on your column and choose "Custom", and set up the windows for less
than, and 1/1/2002

That should find them for you...........then, you can replace them with
whatever text you want, by typeover or copy and paste, etc........

Vaya con Dios,
Chuck, CABGx3

 

[Bree]

Thank you for your response. I tried your formula, but must have made a
mistake somewhere. I don't work with formulas often! When I put in the
formula, I came up with a rather large #. I could try to copy/paste, but
this file is huge! Perhaps a little more detail will help. I copied the
directory listing from our file server into a spreadsheet, so that we can
have a listing of every file on our server, and delete any files prior to
1/1/02. At this point there are somewhere around 90,000 files listed on the
spreadsheet (several spreadsheets) to be sorted. Copying and pasting would
take me forever!! Any help would be great! Thanks for your response
previously.

 

[Max]

The assumption was that you had real *dates*
in col A, in A2 down, say in A2:A6

1-Dec-01
2-Mar-02
1-Apr-02
4-Dec-02
31-Dec-02

Then if you enter the specific date to be checked
in say, B1: 1-Jan-2002

You could put in B2, and copy down:

=IF(A2="","",IF(A2<=$B$1,TEXT(A2,"dd-mmm-yy"),A2))

(Formula slightly revised)

What you'll get in B2:B6 is

01-Dec-01
37317
37347
37594
37621

The big "numbers" 37317, 37347, etc are actually dates
Just format via:

Select col B
Click Format > Cells > Date
Choose "04-Mar-97" (under Type)
Click OK

And you'll now get in B2:B6:

01-Dec-01
02-Mar-02
01-Apr-02
04-Dec-02
31-Dec-02

If I read your set-up correctly,
assuming the source sheet is Sheet1,
try this in Sheet2:

Put in A1:

=IF(Sheet1!A1="","",IF(Sheet1!A1<=DATE(2002,1,1),TEXT(Sheet1!A1,"dd-mmm-yy")
,Sheet1!A1))

(reference date 1-Jan-2002 is now hardcoded)

Copy A1 across and down to cover a range similar
to what is in the source sheet1

Kill all formulas in Sheet2:
Press CTRL+A to select the entire sheet
Right-click > Copy
Right-click > Paste special > values > OK

Repeat above for other source sheets