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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,267
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,765
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! :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,390
Messages
5,836,978
Members
430,464
Latest member
nickburrett

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