Change date format in string?

[Ed]

I have a macro which AutoFilters a date column. The spreadsheet is now
being created differently, and the dates are coming up in a different
format. Currently, I have
If MyTarget = "d" Then
Selection.AutoFilter Field:=5, Criteria1:=">=20020703", _
Operator:=xlAnd, Criteria2:="<=20030616"
End If

Is there any way to change these dates to "03-Jul-02" on the fly? Or do I
need to go into the macro and redo the date for every If?

Ed

 

[Bob Phillips]

Does it not work then. I ask because if the dates are just formatted
differently, the underlying date values are the same.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ed]

No, Bob, it doesn't work. Not only that, but I've been trying desperately
to do everything I can to make it work. I made a copy of the spreadsheet
and changed the format of all these cells to DATE as yyyymmdd. That gave
them the correct look, but they wouldn't filter with "greater than or equal
to" - but they WILL filter on "equal to".

So I copied the entire column into Word as a single-column table, the
repasted back into Excel, just to have everything as pure text. Then I
formatted the cells as NUMBER with no decimals. Same thing: filters on
"equals" but not "greater than".

The only thing I can think of is that I have changed the way I create this
spreadsheet. The previous versions (in which the filter worked fine) were
built from a database query table opened in Excel. The same column there is
also formatted NUMBER, no decimals. But the numbers came out of the
database as "20030202". This sheet, though, is built by searching a text
doc for strings and inserting them into the cell as
"Cell(r,c).Value="string". Doing this, the search finds the date field "02
Feb 2002" and writes it into the cell, where Excel automatically formats it
as a DATE, showing "02-Feb-02", but yielding the date value in the formula
bar.

Can you give me any hints?

Ed

 

[Bob Phillips]

Ed,

Try changing the way the data is retrieved to

Cell(r,c).Value=CDate("string")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ed]

I don't have time tonight to play with that (I'm at work), so I'll get back
to you tomorrow. Thank you for all your help.

Ed

 

[Bob Phillips]

I'll watch out for it. Bedtime here.

Bob

I don't have time tonight to play with that (I'm at work), so I'll get back
to you tomorrow. Thank you for all your help.

Ed

 

[Ed]

Bob: The cell value is lifted as is out of the report as a string. It is
inserted in the proper cell in the worksheet using a loop that works for all
the cells, not just the DATE fields. As it is ("dd-mmm-yy"), it sorts fine;
it just doesn't work with my established macros.

So what I've done is go back to that column when I'm done and run down the
cells with

strLink = ActiveCell.Text

If strLink <> "" Then
strLink = Format(strLink, "yyyymmdd")
ActiveCell.NumberFormat = "General"
ActiveCell.Value = CDec(strLink)
End If

I guess because it lifts it as a date, it accepts the formating to change it
to the number. Then I reinsert it with the CDec (thank you for tipping me
to these formatting codes!), and it goes in as a number that can be sorted.

I don't know if there's a better way, but this is working for now. Thank
you for all your help.

Ed