Illustration: Lisa Hornung, Getty Images/iStockPhoto
The article How to easily include dynamic dates in a Word doc using Excel suggests calculating dates in Excel and using mail merge to a Word template when you need to calculate dates for a Microsoft Word document. The reason? Word’s DATE field isn’t flexible, so when you need more than DATE provides, the Excel route is a good choice. However, if you need only to add or subtract a specific number of days, you can use a VBA procedure instead. In this article, I’ll show you how to use VBA to insert static dates a specific number of days into the future or past.
SEE: 83 Excel tips every user should master (TechRepublic)
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. For your convenience, you can download the demonstration .docm, .doc and .cls files. VBA procedures aren’t supported by Microsoft Word Online.
About Word’s DATE field
Word’s DATE field does exactly what you might expect: It returns the current date. Unlike Excel, which supports a number of date functions, DATE returns only the current date. If you try to come up with an expression that uses DATE, you will most likely fail. It can be done, but it’s so difficult that even experts don’t recommend it.
The DATE field uses the following syntax:
{ DATE [ @ “format”] [Switches] }
An explanation of each follows:
- @ “format”: Specifies a date format other than the default format. If you select a format in the Field dialog box, Word inserts a corresponding format switch. To use a format that’s not listed in the Field dialog box, click the Field Codes button, and type the format switch directly into the Field codes box.
- l: Inserts the date with the last format that you selected in the Date and Time dialog box.
- h: Specifies use of the Hijri/Lunar calendar.
- s: Specifies use of the Saka Era calendar.
It’s unlikely that most of us will ever need the last two, so let’s review an example of the first two. If the current date is Sept. 14, 2021,
{ DATE @ “dddd, MMMM d YYYY” }
will display Tuesday, September 14, 2021.
If you work with Excel or even Access, the format string (“dddd, MMMM d YYYY”) is probably familiar to you. Because of this string, this is one of the times when entering the field via the interface is easier than any other method because the formats will be listed for you and all you do is click one. You won’t have to memorize the many string codes.
Use Word’s DATE field anytime you want to insert a quick date using the Shift + Alt + D shortcut. Doing so enters a DATE field, which will update. When you need more, considering using VBA.
How to enter and run VBA procedures
Before we move on, you’ll need to know how to enter VBA procedures. If you’re familiar with working with VBA code, you can skip this section. Although I use the term procedure, many sources, including Microsoft, use the term macro and sub procedure interchangeably.
To enter the VBA procedures in this article, press Alt + F11 to open the Visual Basic Editor. In the Project Explorer to the left, select ThisWorkbook so you can run the procedure in any sheet. You can enter the code manually in ThisWorkbook or import the downloadable .cls file. The procedures are in the downloadable .docm, .doc and .cls files.
SEE: Windows 10: Lists of vocal commands for speech recognition and dictation (free PDF) (TechRepublic)
If you enter the code manually, don’t paste from this web page. Instead, copy the code into a text editor and then paste that code into the ThisWorkbook module. Doing so will remove any phantom web characters that might otherwise cause errors.
If you are using a ribbon version, be sure to save the workbook as a macro-enabled file. If you’re working in the menu version, you can skip this step.
To run a macro from the interface, do the following:
- Click the Developers tab.
- Click Macros in the Code group.
- In the resulting dialog, choose the procedure and click Run.
Refer to these instructions for entering and running VBA procedures. I won’t repeat these instructions in the following sections.
Now let’s take a look at a sub procedure that returns the current date.
About VBA’s DATE() function
When you need more than a simple date, consider using a VBA procedure. Keep in mind that in VBA, there’s both a DATE() function and a Date data type; we’re reviewing both at this point, and the following function prints the current date in the Immediate window (press Ctrl + G to display this window in the Visual Basic Editor):
Function DateTest()
‘Print today’s date.
‘Call anytime you need the current date.
Dim dDate As Date
DateTest = Date
Debug.Print DateTest
End Function
dDate is a Date variable, and Date in the next line references the system date. You can read the latter at any time using Date. DateTest() doesn’t reset the system date, it only reads it and then displays it in the immediate window. Try not to let the inconsistent case or lack of () characters confuse you. Now that you know the difference between referencing the system date using Date and defining a date variable using Date (I feel dizzy just writing that), let’s move on and insert future and past dates into a Word document.
Calculating future and past dates
You can calculate future and past dates using the DATE field if you have the specialized skill. Most of us don’t, and even those who do, struggle to get the DATE field expression correct. Most turn to VBA because it’s much easier and for almost everyone on Earth, quicker.
Listings A and B are simple sub procedures that insert the date 30 days into the future and the past, respectively, at the cursor’s position. Figure A shows the results for both when the current date is Sep. 17, 2021 (a Friday).
Listing A
Sub Date30Future()
‘Insert date using specific format 30 days
‘into the future of current date.
Selection.TypeText _
Text:=Format(Date + 30, “mmmm d, yyyy”)
End Sub
Listing B
Sub Date30Past()
‘Insert date using specific format 30 days
‘into the past of current date.
Selection.TypeText _
Text:=Format(Date – 30, “mmmm d, yyyy”)
End Sub
Figure A
The above listings are helpful, as far as they go. You can alter either by entering a value other than 30 or -30 in the code, but they are still limited to return only two dates. By adding an InputBox(), you can allow the user to specify the number of days.
How to calculate a passed number of days
Don’t confuse the word passed in the heading with past. Using Listing C users pass an integer value, either positive or negative (future or past, respectively) by passing that value via an input box.
Listing C
Sub DateInput()
‘Allow user to input the number of days
‘to add or subtract from the current date.
Dim days As Integer
days = InputBox(“Enter a positive number to add ” _
& “or a negative number to subtract.”, “Enter date.”)
Selection.TypeText _
Text:=Format(Date + days, “mmmm d, yyyy”)
End Sub
When you run DateInput(), VBA will display the input box show in Figure B. Enter a positive integer to see a date into the future. Enter a negative integer to see a date from the past. Figure C shows the result of entering 5 and -5 on Sept. 17, 2021.
Figure B
Figure C
Beyond the basics
You could go a bit further by creating a userform that lets users choose a future or past date to avoid entering a negative value when you want a past date. You could also add format options. None of the VBA procedures include error-handling, so you’ll want to consider those needs when adding these procedures to your own files.
