Macro + Dates formatting issue

[Guest]

Hi All,

Formatting issue with a macro.

Report with dates formatted as dd.mm.yyyy is changed by

Columns("G:G").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.NumberFormat = "dd/mm/yyyy;@"
Columns("A:N").Select
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B1").Select

BUT, on some cells, the format of the date = mm/dd/yyyy.

Subsequently my sort is incorrect. Seriously going bald over this one.

Any help much appreciated.

Cheers

John

 

[Dave Peterson]

If you have 01.02.2003 in a cell, then do your mass change, you'll be left with
01/02/2003. Excel will see it as a date -- and if your windows short date
format is in mdy order, that cell will become Jan 2, 2003.

If you have 31.01.2003, you'll end up with 31/01/2003 and excel won't even know
that it's a date -- since it doesn't match the windows short date (mdy order).

Instead of using edit|replace, try recording a macro when you select your range.
data|text to columns
fixed width, no lines and no new lines
and choose dmy (to match the data--not to match what you want)

Then apply the formatting you like (dd/mm/yyyy)

And sort your data.

And stop the recorder.

 

[Guest]

Spot on Dave,
Many thanks

If you have 01.02.2003 in a cell, then do your mass change, you'll be left with
01/02/2003. Excel will see it as a date -- and if your windows short date
format is in mdy order, that cell will become Jan 2, 2003.

If you have 31.01.2003, you'll end up with 31/01/2003 and excel won't even know
that it's a date -- since it doesn't match the windows short date (mdy order).

Instead of using edit|replace, try recording a macro when you select your range.
data|text to columns
fixed width, no lines and no new lines
and choose dmy (to match the data--not to match what you want)

Then apply the formatting you like (dd/mm/yyyy)

And sort your data.

And stop the recorder.