Replace dots in dates by slashes

Question

When analyzing data in Excel there are a lot of dates that I pull into the worksheet in the dd.mm.yyyy format. I want to convert these dates to a dd/mm/yyyy format.

When doing this manually (ctrl+f => replace . by /) Excel gives me the correct European dates. When using a macro Excel gives me American dates whenever the day is equals 12 or lower and European dates when the day is above 12.

The macro used is the regular search and replace macro (it has even been recorded to make sure that it does the exact same this as when I am recording the macro). When recording the macro, Excel gives me European dates and when running it a mix between European and American dates):

Range("A1:A7").Select


Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Manually replacing gives me following output:

=> European to European

Using the exact same (recorded or written) macro excel gives me the following output

=> European to mix of American and European

I am replacing this manually for months now and cannot incorporate any date replacing macros in my code.

Try: `DateValue(Replace(sMyDate, ".", "/"))`.. where `sMyDate` is the value from your cells. Also please readup on why we shouldn't use [Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Zac, Oct 04 '19 at 12:16
When you say that you "cannot incorporate any date replacing macros in my code" do you mean that you are not able to or do not want to? Not wanting to is a perfectly valid reason — Marcucciboy2, Oct 04 '19 at 12:24
I mean I cannot incorporate any macro replacing dates in my code. I can now, thank you all!! — Leo, Oct 04 '19 at 12:45

score 3 · Answer 1 · answered Oct 04 '19 at 12:21

The solution is on Windows: don't let Excel do the conversion, but let Windows do the conversion:

Go to Control Panel, Region and Language
Click "Additional Settings"
Click on the Date tab
Replace the Short Date format with dd.MM.yyyy
Click Apply and click OK

Now import your data. Excel will set your date cells to the "Date" format (if you look at the format in Format Cells, it has a "*" before the format, meaning it will change with the Operating System seting). When done:

click again "additional Settings" and click the Date tab
Change the Short Date format to dd/MM/yyyy
Click Apply and OK and click Apply again and OK again to close the Region and Language dialog

Done! (anyway, on my Excel 2003 the dates are now as you want them)

Replace dots in dates by slashes

1 Answers1