Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-21-2021, 01:28 AM
Shelley Lou Shelley Lou is offline VBA help with Date Format change period to forward slash for abbreviated dates Windows 10 VBA help with Date Format change period to forward slash for abbreviated dates Office 2016
Competent Performer
VBA help with Date Format change period to forward slash for abbreviated dates
 
Join Date: Dec 2020
Posts: 170
Shelley Lou is on a distinguished road
Default VBA help with Date Format change period to forward slash for abbreviated dates

Hi, I've written a little macro to convert date format to our current house style, so the macro removes any ordinal numbers, inserts non breaking spaces and removes the word 'the' before dates where ordinals have been removed. The bit I'm stumped on is where dates are abbreviated with periods to change the period to a forward slash but I keep getting a bug error that the replace text is out of range. Any ideas where I'm going wrong? I've also noticed the code does not work when there are tables in the document but not sure how to tell the code to loop through any tables.



test doc for dates.docx

Code:
Sub DPU_TestDateFormat()
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = ActiveDocument.Range
  With Rng.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Format = False
    .Forward = True
    .Wrap = wdFindContinue
    .MatchWildcards = True
    .Text = "([0-9]{1,2})([dhnrst]{2})( [JFMASOND][anuryebchpilgstmov]{2,8} [12][0-9]{4}>)"
    .Replacement.Text = "\1\3"                  'Removes ordinal numbers for dates
    .Execute Replace:=wdReplaceAll
    .Text = "(<[0-9]{1,2})[^s ]([JFMASOND][anuryebchpilgstmov]{2,8})[^s ]([0-9]{4}>)"
    .Replacement.Text = "\1^s\2^s\3"            'Non breaking spaces between dates
    .Execute Replace:=wdReplaceAll
    .Text = "([09]{1,2}.[0-9]{1,2}.[0-9]{2,4})"  'Replace periods with forward slash for abbrev. dates
    .Replacement.Text = "\1^47\2^47\3"
    .Execute Replace:=wdReplaceAll
    .Text = "the ([0-9]{1,2})[^s ]([JFMASOND][anuryebchpilgstmov]{2,8})"
    .Replacement.Text = "\1^s\2"                  'Remove 'the' before dates
    .Execute Replace:=wdReplaceAll
    End With
    Application.ScreenUpdating = True
    End Sub

Last edited by Shelley Lou; 10-21-2021 at 11:08 AM.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA help with Date Format change period to forward slash for abbreviated dates Selecting a Date in Date Picker and Having it change dates throughout joshuaran Word VBA 2 03-14-2018 09:24 PM
VBA help with Date Format change period to forward slash for abbreviated dates Change Date Format in Gantt Chart culverjack Visio 2 11-08-2016 08:12 AM
Count the common time period (month) between two date period of time Barni Excel 6 08-15-2014 07:52 AM
Date Format: how to output dates that are not US? tinfanide Word VBA 0 10-28-2013 03:30 AM
How to change number format of dates ketanco Project 1 09-11-2012 02:34 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:01 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