Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #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
Expert
VBA Help with Time am/pm Format
 
Join Date: Dec 2020
Posts: 259
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
 



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 12:22 PM.


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