Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-18-2021, 09:30 AM
Shelley Lou Shelley Lou is offline VBA Help with Time am/pm Format Windows 10 VBA Help with Time am/pm Format Office 2016
Competent Performer
VBA Help with Time am/pm Format
 
Join Date: Dec 2020
Posts: 163
Shelley Lou is on a distinguished road
Default 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

Reply With Quote
  #2  
Old 10-18-2021, 03:26 PM
Guessed's Avatar
Guessed Guessed is offline VBA Help with Time am/pm Format Windows 10 VBA Help with Time am/pm Format Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #3  
Old 10-19-2021, 12:22 AM
Shelley Lou Shelley Lou is offline VBA Help with Time am/pm Format Windows 10 VBA Help with Time am/pm Format Office 2016
Competent Performer
VBA Help with Time am/pm Format
 
Join Date: Dec 2020
Posts: 163
Shelley Lou is on a distinguished road
Default 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
Reply With Quote
  #4  
Old 10-19-2021, 03:13 PM
Guessed's Avatar
Guessed Guessed is offline VBA Help with Time am/pm Format Windows 10 VBA Help with Time am/pm Format Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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.
Reply With Quote
  #5  
Old 10-20-2021, 08:11 AM
Shelley Lou Shelley Lou is offline VBA Help with Time am/pm Format Windows 10 VBA Help with Time am/pm Format Office 2016
Competent Performer
VBA Help with Time am/pm Format
 
Join Date: Dec 2020
Posts: 163
Shelley Lou is on a distinguished road
Default 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
Reply With Quote
Reply

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
VBA Help with Time am/pm Format How to set the table cells as time format? jiaron_1230 Word VBA 3 11-15-2016 05:15 AM
VBA Help with Time am/pm Format 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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:36 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft