Issue with American Format when VBA "Find and Replace"

[Lars_C]

Hey there,

Apologies, this seems very stupid. I am fully aware of the issue with the American date when using VBA find and replace function. I have read about it and took onboard that others suggested, but it still doesn't work for me.

I might be doing something very silly here, but any chance someone could have a quick check of my simple code please.

Thank you,
Lars

Sub FormatDate()
'
' FormatDate Macro
'
    Columns("A:A").Select
    Selection.NumberFormat = "dd/mm/yyyy;@"
    Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

 

[James006]

Hi,

You know your dates are actually numbers ... which are displayed with a custom format ...

As long as your column A is holding numbers ... for your macro is concerned ...you can test following

Sub FormatDate()
'
' FormatDate Macro
    Columns("A:A").NumberFormat = "dd mm yyyy"
End Sub

The trick is that you will have to go to your Control Panel ....
Customize Regional Options > Date > Date separator

HTH

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] FormatDate()
[color=green]'[/color]
[color=green]' FormatDate Macro[/color]
[color=green]'[/color]
    [color=darkblue]With[/color] Columns("A:A")
        .NumberFormat = "dd/mm/yyyy;@"
        .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
            FieldInfo:=Array(1, 4), TrailingMinusNumbers:=[color=darkblue]True[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Lars_C]

Thank you both for helping me on this.

James, unfortunately I couldn't get your suggestion to work. It reformats the date fine, but does not replace the ".". I tried linking that with my script, but not successful.

AlphaFrog, your suggestion worked well. The only thing is that if I run the macro twice, it will swap between the American and British format, e.g. 01/08/2017 becomes 08/01/2017 and back around again.
It is not a big deal, but out of interest how would I limit the action just to show the correct format, rather than switch in between?

Thanks again,
Lars

 

[James006]

James, unfortunately I couldn't get your suggestion to work. It reformats the date fine, but does not replace the ".". I tried linking that with my script, but not successful.

As already explained, the Date separator is a Windows set-up ... not an Excel feature ...

So, you need to adjust your Regional settings ...

 

[AlphaFrog]

AlphaFrog, your suggestion worked well. The only thing is that if I run the macro twice, it will swap between the American and British format, e.g. 01/08/2017 becomes 08/01/2017 and back around again.
It is not a big deal, but out of interest how would I limit the action just to show the correct format, rather than switch in between?

I can't duplicate the toggle effect. Not sure how to fix it if I could.

 

[Lars_C]

Okay, no worries. Your suggestions will do the job nicely.

Thanks again,
Lars