Roll Up/Roll Back Dates

The information in this website is provided without risk or obligation and free of charge. However, if you have benefitted from my efforts here and would like to make a contribution to help me continue and maintain this work then any donation will be greatly appreciated. Please click the adjacent button to access PayPal. Thank you.

Microsoft Word users frequently ask, "How do I calculate and insert a future or past date in a document." An example would include 30, 60, and 90 day payment due following a dated sale.

Several Word MVP colleagues and newsgroup contributors have addressed this topic in newsgroups responses or through their personal websites. In fellow MVP and friend Graham Mayor's website he explains why adding a number to a date value in a simple calculation field provides disappointing results as well as introducing his readers (via link and examples) to a brilliant collection of methods for performing calculations, including date calculations, using Word fields perfected by a regular newsgroup contributor that goes by "Macropod" . This tips page in contrast, provides various macro solutions using VBA or a combination of VBA and fields.

If you are interested in the pure field methods that work brilliantly but appear complex, then a visit to Graham's article or to the articles by Macropod are two good places to to get you started:

Graham Mayor's - Insert a future (or past) date in Word documents

Macropod's - Date Calculations in Word

Ok, so you want to try some VBA/field methods.

Let's start with the example given above. You have a simple Word table where a sale date is generated by a CREATEDATE field. You want to calculate and enter the 30, 60, and 90 day payment due dates.

First the fields. In the illustration above the shaded areas are text generated from fields. Here is the same sample text with the field codes displayed:

Bonus Tip: ALT+F9 toggles field code display. For more on creating and using fields in your Word documents see: Word Fields

The DocVariables "Date1, Date2, and Date3" used in the above fields are values created with VBA and stored in the document. Here is the VBA macro code that performs the calculation and creates the variables:

Sub DateAdd()
Dim myDate As Date
Dim myRng As Range
'Set the starting date with the value of a field
Set myRng = ActiveDocument.Fields(1).Result
myDate = myRng
'Tip - Here the field index is one because it is the first field in the document.
'If you don't know the field index number? Just select the field and run the following line of code:
'MsgBox Selection.Fields(1).Index
With ActiveDocument.Variables
   .Item("Date1").Value = Format(myDate + 30, "dd MMMM yyyy")
   .Item("Date2").Value = Format(myDate + 60, "dd MMMM yyyy")
   .Item("Date3").Value = Format(myDate + 90, "dd MMMM yyyy")
End With
ActiveDocument.Fields.Update
End Sub

The MacroButton field is used to fire the macro code (double-click it) or see:
Assign a Word macro to a toolbar or menu and Assign a Word macro to a hot-key

Need help applying a macro? See fellow MVP Graham Mayor's tips here: Installing Macros From Listings

As an alternative, you could eliminate the CREATEDATE field and use any manually entered date in the top cell as shown in the following illustration and VBA code:

Sub DateAdd()
Dim myDate As Date
Dim myRng As Range
'Set the starting date with the content of a table cell
On Error GoTo ErrorHandler
Set myRng = ActiveDocument.Tables(2).Cell(2, 2).Range
myDate = Left(myRng, Len(myRng) - 2)
With ActiveDocument.Variables
   .Item("Date4").Value = Format(myDate + 30, "dd MMMM yyyy")
   .Item("Date5").Value = Format(myDate + 60, "dd MMMM yyyy")
   .Item("Date6").Value = Format(myDate + 90, "dd MMMM yyyy")
End With
ActiveDocument.Fields.Update
Exit Sub
ErrorHandler:
MsgBox "You did not enter a valid date or the cell range does not exist!"
End Sub

Or you could use the text of a named bookmark as shown in the following illustration and VBA Code:

Sub DateAdd()
Dim myDate As Date
Dim myRng As Range
'Set the start date with bookmarked text
On Error GoTo ErrorHandler
Set myRng = ActiveDocument.Bookmarks("checkOutDate").Range
myDate = myRng
With ActiveDocument
.Variables("Date7").Value = Format(myDate + 10, "mm/dd")
.Fields.Update
End With
Exit Sub
ErrorHandler:
MsgBox "You did not enter a valid date or the bookmark has been deleted!"
End Sub

You may simply want to enter a date offset (past or future) from the current date at the insertion point. The following macro enters the required date as plain text:

Sub OffsetDate()
Dim pOffset As String
On Error GoTo ErrorHandler
pOffset = InputBox("Enter direction and amount of offset" _
& vbCr & "(e.g., -7 to roll back 7 days or perhaps" _
& vbCr & "+10 to roll forward 10 days).", "Offset")
Selection.InsertBefore Format(Date + pOffset, "dd MMMM yyyy")
Exit Sub
ErrorHandler:
MsgBox "You did not enter a valid roll back\roll forward value!"
End Sub

You can also use a Word "protected" or "on-line" form. The following illustration gives an example of a protected form using a contractDate field and a returnDue field. The returnDue field value is generate by an on exit macro set to run upon exit of the contractDate field.

Sub returnDue()
Dim myDate As Date
On Error GoTo ErrorHandler
myDate = ActiveDocument.FormFields("contractDate").Result
ActiveDocument.FormFields("returnDue").Result = myDate + 15
Exit Sub
ErrorHandler:
MsgBox "You did not enter a valid contract date"
Selection.GoTo What:=wdGoToBookmark, Name:="contractDate"
End Sub

Of course the above examples are just a few of the methods that you could use. If you are interested in exploring other interesting uses of VBA for generating dates and calculating time then check out my Word Add-In "Date Sequencer" at the following link: Date Sequencer

Looking for something else?