Hi Guys, can anyone help me with a time format macro I'm trying to create. The part I'm having problems with is how to change a period to a colon between the time digits - the time format should be e.g. 9:00am, 9:00pm not 9.00 am / 9.00 pm or if the document is displaying 24 hr then 09:00, 18:00
So the macro below so far removes periods from a.m/p.m to am/pm but need to update this to include uppercase A.M/P.M to lowercase am/pm and also removes the space between the digits of am/pm so e.g. 9.00 am becomes 9:00am etc.
test doc for times.docx
Code:
Sub DPU_TimeFormat()
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = ActiveDocument.Range
With Rng.Find
.MatchWildcards = True
'Delete periods in a.m./p.m.
.Text = "[^s ]([ap]).m."
.Replacement.Text = "^s\1m"
.Execute Replace:=wdReplaceAll
.Text = "[^s ]([ap]).m>"
.Execute Replace:=wdReplaceAll
'Delete spaces in # am/pm
.Text = "([0-9])[^s ]([ap]m)"
.Replacement.Text = "\1\2"
.Execute Replace:=wdReplaceAll
'Change period for colon in times
.Text = "([0-9]{1,2}.[0-9]{1,2}[ap]m)"
.Replacement.Text = "????"
.Execute Replace:=wdReplaceAll
End With
Application.ScreenUpdating = True
End Sub