#1
|
|||
|
|||
VBA Help with Time am/pm Format
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 |
#2
|
||||
|
||||
Since it is a macro it doesn't matter if it takes a few extra passes
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 = "([0-9]) ([APap]).([mM])." 'with space between number and a/p .Replacement.Text = "\1\2\3" .Execute Replace:=wdReplaceAll .Text = "([0-9]) ([APap]).([mM])" .Replacement.Text = "\1\2\3" .Execute Replace:=wdReplaceAll .Text = "([0-9]) ([APap])([mM])>" 'without space between number and a/p .Replacement.Text = "\1\2\3" .Execute Replace:=wdReplaceAll .Text = "([0-9])AM>" 'AM to am .Replacement.Text = "\1am" .Execute Replace:=wdReplaceAll .Text = "([0-9])PM>" 'PM to pm .Replacement.Text = "\1pm" .Execute Replace:=wdReplaceAll 'Change period for colon in times .Text = "([0-9]{1,2}).([0-9]{2})([ap])m" .Replacement.Text = "\1:\2\3m" .Execute Replace:=wdReplaceAll End With Application.ScreenUpdating = True End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
VBA Help with Time am/pm format
Hi Andrew, many thanks for updating the code much appreciated, I nearly got there but not quite! Have just run it and all working great. I forgot to add a few steps to convert e.g. 8am/5pm to 8:00am/5:00pm and the variations as below to include capital AM/PM :
Change 8am or 5pm to 8:00am or 5:00pm Change 8 am or 5 pm to 8:00am or 5:00pm Change 8a.m. or 5p.m. to 8:00am or 5:00pm Change 8 a.m. or 5 p.m. to 8:00am or 5:00pm How can I add these to the current macro? Much appreciated, Shelley |
#4
|
||||
|
||||
That changes things a bit. I've modified the earlier searches and added a new section at the end
Code:
Sub DPU_TimeFormat2() Dim Rng As Range Application.ScreenUpdating = False Set Rng = ActiveDocument.Range With Rng.Find .MatchWildcards = True 'Delete periods in a.m./p.m. .Text = "([APap]).([mM])." .Replacement.Text = "\1\2" .Execute Replace:=wdReplaceAll .Text = "([APap]).([mM])" .Replacement.Text = "\1\2" .Execute Replace:=wdReplaceAll .Text = "([0-9]) ([APap])([mM])>" 'without space between number and a/p .Replacement.Text = "\1\2\3" .Execute Replace:=wdReplaceAll .Text = "([0-9])AM>" 'AM to am .Replacement.Text = "\1am" .Execute Replace:=wdReplaceAll .Text = "([0-9])PM>" 'PM to pm .Replacement.Text = "\1pm" .Execute Replace:=wdReplaceAll 'Change period for colon in times .Text = "([0-9]{1,2}).([0-9]{2})([ap])m" .Replacement.Text = "\1:\2\3m" .Execute Replace:=wdReplaceAll 'Expand abbreviated hours .Text = " ([0-9]{1,2})([ap])m" .Replacement.Text = " \1:00\2m" .Execute Replace:=wdReplaceAll End With Application.ScreenUpdating = True End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia Last edited by Guessed; 10-20-2021 at 01:46 PM. |
#5
|
|||
|
|||
VBA Help with Time am/pm format
Andrew, thank you so much for the updated code, it works perfectly. I initially searched the internet high and low for something like this but there was nothing at all for Word documents so hopefully this will benefit someone else looking to format times in documents. Thank you again for you help and patience as ever, best wishes, Shelley
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Format number for time , hh:mm:ss,,,second appears 00 | yoshi | Excel | 4 | 04-26-2021 03:11 AM |
How to set the table cells as time format? | jiaron_1230 | Word VBA | 3 | 11-15-2016 05:15 AM |
Changing Date/time to Australia format | tmeck63 | Project | 1 | 01-07-2016 09:11 PM |
time in minute format | orajesh | Excel | 3 | 12-15-2015 12:56 AM |
Changing format of time data | Sammael | Excel | 2 | 04-08-2012 12:13 PM |