Microsoft Office Forums - View Single Post - [Solved] Add 21 days onto Auto Update field

Charles Kenyon · 12-13-2024, 11:27 AM

Stu,

I appreciate your frustration. This may be easy for Paul. He mastered it long ago and provided a wonderful reference. If you use it as intended, you can get what you want.

Calendar math is far from simple. You cannot simply type calculated date fields because dates do not follow ordinary math rules.

The following is from my page on calculated date fields:
Again, Paul's tutorial is the reference source on these.

Adding 21 days to a date is not simple.

Creation date: July 9, 2015 - desired field result = July 30, 2015
This one is easy because it is in the same month and year. It could be done with a simple field.

Creation date: June 23, 2014 - desired field result = July 14, 2014
Creation date: July 23, 2014 - desired field result = August 13, 2014
Oops: Still in the same year, but a different month. How many days were there in the original month?

Creation date: December 20, 2014 - desired field result = January 10, 2015
Oops again, not only a different month but a different year.

Creation date: February 27, 2015 - desired field result = March 19, 2015
Creation date: February 27, 2016 - desired field date result = March 20, 2016
Again, a different month. And, how many days are there in February?

What to do:

Download Paul's tutorial. Read the introductory material.
Read about the different kinds of date fields.
The tutorial goes into Julian and Gregorian calendar dates. You do not need to understand this to use the fields in the tutorial. You may need it to understand the construction of some of the fields, but not to use them.
Do use the macro in the tutorial to adjust your date format if different.
Copy one of Paul's base fields into a document. I suggest the one for Calculate a day, date, month and year, using n days delay.
Toggle the display of field codes using Alt+F9 or Fn+Alt+F9.
Change the Delay in that field from 14 to 21
Toggle the display of field codes back to showing results.
Press F9 (or Fn+F9) to update the field.

Again, I suggest changing the DATE field in that to a CREATEDATE field and having this in a template.

You can use a macro for this if fields are too complex.
Calculated dates are easiest performed by a short macro that has the following code -

Code:

Selection.InsertBefore Format((Date + 21), "d MMMM yyyy")

will enter the current date + 14 at the cursor.

Here is a more complex one from MVP Graham Mayor:

Code:

Sub InsertFutureDate()    'Graham Mayor
    ' Written by Graham Mayor and posted on the word.docmanagement
    '   newsgroup in March 2000
    ' Inserts a future date in a document - note that this is not a field
    ' Some style revisions and error handler by Charles Kenyon
    '
    Dim message As String
    Dim Mask   As String  ' Format for date in form "MMMM d, YYYY" will produce December 13, 2024 format
    Dim title  As String
    Dim Default As String ' Number of days to advance date
    Dim Date1  As String
    Dim myValue As Variant
    Dim MyText As String
    Dim Var1   As String
    Dim Var2   As String
    Dim Var3   As String
    Dim Var4   As String
    Dim Var5   As String
    Dim Var6   As String
    Dim Var7   As String
    Dim Var8   As String
    '
    Mask = "d MMMM yyyy"    ' Set Date format
    Default = "21"   ' Set default number of days.
    title = "Plus or minus date starting with " & Format(Date, Mask)
    Date1 = Format(Date, Mask)
    Var1 = "Enter number of days by which to vary above date. " _
           & "The number entered will be added to "
    Var2 = Format(Date + Default, Mask)     ' Today plus default (60)
    Var3 = Format(Date - Default, Mask)     ' Today minus default (60)
    Var4 = ".     The default ("
    Var5 = ") will produce the date "
    Var6 = ".  Minus (-"
    Var7 = ".  Entering '0' (zero) will insert "
    Var8 = " (today).  Click cancel to quit."
    MyText = Var1 & Date1 & Var4 & Default & Var5 & Var2 & Var6 _
             & Default & Var5 & Var3 & Var7 & Date1 & Var8
    '
    '   Display InputBox and get number of days
GetInput:
    myValue = InputBox(MyText, title, Default) ' uses variable set above
    '
    If myValue = "" Then
        End    'quit subroutine
    End If
    '
    On Error GoTo oops    ' just in case user typed non-number
    Selection.InsertBefore Format((Date + myValue), Mask)
    Selection.Collapse (wdCollapseEnd)
    End    'End subroutine
    '
oops:        ' error handler in case user types something other than a number
    '
    MsgBox Prompt:="Sorry, only a number will work, please try again.", _
           buttons:=vbExclamation, _
           title:="A number is needed here."
    GoTo GetInput
End Sub

You can get this very fancy if you wish, having it insert at bookmarked locations. From my page, again...

So how to automatically place the calculated date?
The method I tend to prefer is to use a bookmark in conjunction with an AutoNew macro that runs automatically on creating the new document from the invoice template.
Bookmark the place the due date will appear. Call the bookmark 'DueDate'.
You need an extra line of code for the macro to locate the bookmark, and you may need to change the date mask to suit local requirements. As printed below it will type '12 September 2000'

Code:

Selection.GoTo What:=wdGoToBookmark, Name:="DueDate" 
Selection.InsertBefore Format((Date + 21), "d MMMM yyyy")