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

InfoH

Board Regular
Joined
Nov 1, 2011
Messages
60
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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?

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

InfoH

Board Regular
Joined
Nov 1, 2011
Messages
60
Hi,

Just tried that and it didn't fix it

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

InfoH

Board Regular
Joined
Nov 1, 2011
Messages
60
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
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

Board Regular
Joined
Nov 1, 2011
Messages
60
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top