none
Programmatic identification of dates that are in the past RRS feed

  • Question

  • In many business documents, it is necessary to identify past dates. Is there a way to identify dates in varying forms (yyyy-mm-dd, mm/dd, dd-MON-yy etc) and highlight or otherwise change the formatting of the date text if the date is less than the current system date?

    Appreciate the help - thanks.

    Wednesday, May 20, 2015 3:52 PM

Answers

  • Hi Torben

    It would certainly be possible, although perhaps not non-trivial. There's nothing built-into Word or its object model to support the idea, meaning you'd have to define all the various date formats you're interested in. You should then be able to leverage Word's built-in FIND - which supports "wild card" searches (similar to RegEx) to search for occurrences and apply formatting.

    Beyond that, it would help to know in which programming environment / languages you're "at home" so that we can refine possible approaches.

    Also, which Word / document file format versions are relevant?


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by L.HlModerator Tuesday, June 2, 2015 8:02 AM
    Wednesday, May 20, 2015 5:41 PM
    Moderator

All replies

  • Hi Torben

    It would certainly be possible, although perhaps not non-trivial. There's nothing built-into Word or its object model to support the idea, meaning you'd have to define all the various date formats you're interested in. You should then be able to leverage Word's built-in FIND - which supports "wild card" searches (similar to RegEx) to search for occurrences and apply formatting.

    Beyond that, it would help to know in which programming environment / languages you're "at home" so that we can refine possible approaches.

    Also, which Word / document file format versions are relevant?


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by L.HlModerator Tuesday, June 2, 2015 8:02 AM
    Wednesday, May 20, 2015 5:41 PM
    Moderator
  • Thanks for your reply Cindy.

    To answer your questions, I wouldn't say I'm "at home" with any particular environment or language. I've hacked a few things together thanks to this forum and others in Visual Studio / C#. I was interested in the Word automation template that exists for Office .Net development in VS.

    It seems to me there exists logic that senses / interprets dates using REGEX or similar that could be reused from Outlook.There are apps that sense action items or dates which can be added to the calendar. I'm just not sure how to implement that. Excel also has the ability to interpret input as dates.

    Seems like this is something that Word could use as a feature - I can't be the only person facing the daunting task of sifting through pages of content looking for deliverable dates that have elapsed.

    Tuesday, June 2, 2015 12:09 PM
  • Hi Torben

    OK, that would be the VSTO template. And it sounds like you're more comfortable in C# than VB.NET (the two languages supported by the VSTO tools).

    Excel and Outlook are, of course, very different applications that Word. Word focuses primarily on delivering textual, "flowed" content and not data analysis or tracking of dates. No question that the scenario of locating and doing something with dates is something a certain sector would want to do with Word documents, but since it's not really central to what Word "does", that's not something for which there's any special functionality in the product. Closest would probably have been the "SmartTag" (deprecated) that tagged dates in documents...

    Unfortunately, I don't think the code for that was open-source - it would have made a good starting point for you.

    <<sifting through pages of content looking for deliverable dates that have elapsed>>

    Is this something that needs to be done while someone is looking at a document in the Word application? Or more something you'd want to do across multiple files and possibly bring to someone's attention or otherwise perform an action?

    If the latter, it would probably make more sense to analyse the file's Open XML, which can be done server-side without needing the Word application present or running.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, June 2, 2015 1:45 PM
    Moderator
  • Hi again Cindy.

    I think I've got a working prototype but it's inconsistent. It's actually using VBA this time (not my greatest strength mind you). I've got it finding and formatting the date as bold, but it's not finding them all.

    Sub Demo2()
    Application.ScreenUpdating = False
    With ActiveDocument.Range.Find
        .ClearFormatting
        .Text = "([0-9]{1,4})([/-]{1})([0-9]{1,4})([/-]{1})([0-9]{2,4})"
        With .Replacement
          .ClearFormatting
          .Text = "^&"
          .Font.Bold = True
        End With
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchWildcards = True
        .Execute Replace:=wdReplaceAll
    End With
    End Sub

    The issue seems to be that when there are uncommitted changes (with track changes turned on), it is ignoring those elements until they're committed. Is there a way to ignore the uncommit status of changes?

    Other things I'm pursuing but don't have working:

    • Changing the date format in the event of single digit day/month without leading zero padding
    • Appending four digit year in the event of a date format of mm/dd only
    • Changing the date format in the event of a 2 digit year
    • Applying the formatting only when the finally formatted date is before the system date

    To address the first two, I tried swapping out 

    .Text = "^&"

    with

    .Text = Format(.Text, "MM/DD/YYYY")

    or

    .Text = Format("^&", "MM/DD/YYYY")

    But to no avail. Interestingly, this

    Sub LongDate()
    With Selection
       .Text = Format(.Text, "MM/DD/YYYY")
    End With
    End Sub

    works as a separate SUB but not sure how to integrate it into Demo2, and eliminate the need for defining a selection interactively.

    Again, I have't a clue how to integrate the logic of date comparison, but the pseudo code follows. Any thoughts on implementation are certainly appreciated.

    Sub Demo2()
    Application.ScreenUpdating = False
    With ActiveDocument.Range.Find
        .ClearFormatting
        .Text = "([0-9]{1,4})([/-]{1})([0-9]{1,4})([/-]{1})([0-9]{2,4})"
        With .Replacement
          .ClearFormatting
          .Text = Format("^&", "MM/DD/YYYY")
           If .Text < Date Then .Font.Italic = True
        End With
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchWildcards = True
        .Execute Replace:=wdReplaceAll
    End With
    End Sub
    Tuesday, June 2, 2015 6:20 PM
  • Well it's not the most elegant solution, but it alters the date formatting correctly, and highlights dates in the past.

    Dim rng As Range
    Dim the_date As Date
    Selection.HomeKey wdStory
    With Selection.Find
        Do While .Execute(FindText:="([0-9]{1,4})([/-]{1})([0-9]{1,4})([/-]{1})([0-9]{2,4})", Forward:=True, _
        MatchWildcards:=True, Wrap:=wdFindStop) = True
            With Selection
            .Text = Format(.Text, "MM/DD/YYYY")
            the_date = Format(.Text, "MM/DD/YYYY")
            End With
        If DateDiff("d", Date, the_date) < 0 Then Selection.Range.HighlightColorIndex = wdYellow
        Selection.Collapse wdCollapseEnd
    Loop
    End With
    Application.ScreenUpdating = True
    End Sub

    Tuesday, June 2, 2015 7:45 PM