Ask a new question

Macro to convert date format in a document?

I want a macro to convert date format so that “11/5/2011”, for example, or “11/05/2011” would become  “11 May 2011”.

Instead the macro I'm trying out gives me the following:

11/5/2011 should be 11 May 2011 but instead I get 5 November 2011
12/5/2011 should be 12 May 2011 but instead I get 5 December 2011
13/5/2011 should be 13 May 2011 but instead I get 13 May 2011
14/5/2011 should be 14 May 2011 but instead I get 14 May 2011

The macro code is given below, and I would be most grateful if someone can tell me how to correct it.

Sub ConvertDateFormat()

    Dim FoundOne As Boolean

    Selection.HomeKey Unit:=wdStory, Extend:=wdMove

    FoundOne = True ' loop at least once

    Do While FoundOne ' loop until no date is found

        With Selection.Find

            .ClearFormatting

            .Replacement.ClearFormatting

            .Text = "([0-9]{1,2})[/]([0-9]{1,2})[/]([0-9]{4})"

            .Format = True

            .Forward = True

            .MatchWildcards = True

        End With

        Selection.Find.Execute Replace:=wdReplaceNone

        ' check that the find is a date

        If IsDate(Selection.Text) Then

            Selection.Text = Format(Selection.Text, "d MMMM yyyy")

            Selection.Collapse wdCollapseEnd

        Else ' not a date - end loop

            FoundOne = False

        End If

    Loop

End Sub

I would also like to check how I should alter the code to give me a version of the macro that does not loop but simply finds the next date, so that I would then click the macro’s button each time to execute and go to next. This would be useful in cases where I prefer to check each instance rather than just run the macro for the full document.

My regional settings in Windows (8.1) are as follows:

Language: English (South Africa)

Short date: yyy-MM-dd

Long date: dd MMMM yyyy
Answer
Answer

Does this work better for you?

Sub ConvertDateFormat()
    Dim arr
    Dim d As Date
    Selection.HomeKey Unit:=wdStory, Extend:=wdMove
    With Selection.Find
        .ClearFormatting
        .Text = "([0-9]{1,2})[/]([0-9]{1,2})[/]([0-9]{4})"
        .Format = True
        .Forward = True
        .MatchWildcards = True
        Do While .Execute
            arr = Split(Selection.Text, "/")
            On Error GoTo SkipHere
            d = DateSerial(arr(2), arr(1), arr(0))
            Selection.Text = Format(d, "d MMMM yyyy")
SkipHere:
            Selection.Collapse wdCollapseEnd
        Loop
    End With
End Sub

and without the loop:

Sub ConvertDateFormat()
    Dim arr
    Dim d As Date
    Selection.HomeKey Unit:=wdStory, Extend:=wdMove
    With Selection.Find
        .ClearFormatting
        .Text = "([0-9]{1,2})[/]([0-9]{1,2})[/]([0-9]{4})"
        .Format = True
        .Forward = True
        .MatchWildcards = True
        .Execute
        arr = Split(Selection.Text, "/")
        On Error GoTo SkipHere
        d = DateSerial(arr(2), arr(1), arr(0))
        Selection.Text = Format(d, "d MMMM yyyy")
SkipHere:
        Selection.Collapse wdCollapseEnd
    End With
End Sub

---
Best wishes, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated February 3, 2023 Views 2,815 Applies to: