Replacing .'s to /'s on a date format, however it reverts it to US date format??

[InfoH]

So in column B I have a date in the format of dd.mm.yyyy
when doing it manually I highlight the column and find and replace . with /
this works fine, however when putting that into a macro I record the macro and run it back to confirm its working and for some reason instead of changing 01.07.2015 to 01/07/2015 its changing the date to 07/01/2015

any suggestions on a quick fix for it?

heres the segment of vba for that part of the code:

Columns("B:B").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Thanks

 

[Fishboy]

So in column B I have a date in the format of dd.mm.yyyy
when doing it manually I highlight the column and find and replace . with /
this works fine, however when putting that into a macro I record the macro and run it back to confirm its working and for some reason instead of changing 01.07.2015 to 01/07/2015 its changing the date to 07/01/2015

any suggestions on a quick fix for it?

heres the segment of vba for that part of the code:

Columns("B:B").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Thanks

Hi InfoH,

Have you tried specifying the cell format at the end of the macro?

Columns("B:B").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
Selection.NumberFormat = "dd/mm/yyyy;@"

 

[InfoH]

Hi,

Just tried that and it didn't fix it

Still coming out as 07/01/2015 when its actually 1st July

 

[InfoH]

incase anyone else gets this kind of issue heres how I fixed it:

Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(RIGHT(RC[-6],4),MID(RC[-6],4,2),LEFT(RC[-6],2))"

bit of a messy work around.. but if its stupid and it works it isn't stupid!

 

[SpillerBD]

I don't think your original value was seen as a date-serial number but as text. Then when you changed .'s to /'s, Excel used your system settings to see it as a date and then swapping for your cell's date formatting.
Because it looks like a date doesn't mean its a date..
Because it looks like a number doesn't mean its a number...

 

[InfoH]

I don't think your original value was seen as a date-serial number but as text. Then when you changed .'s to /'s, Excel used your system settings to see it as a date and then swapping for your cell's date formatting.
Because it looks like a date doesn't mean its a date..
Because it looks like a number doesn't mean its a number...

That would make sense, it was an extract out of a system into a CVS format so that sounds more than likely to be honest.

Thanks, nice to know what was causing it!