Microsoft Word
FAQ
(frequently asked questions)
Supplement
on ADD Balance by Charles Kenyon
with input and suggestions from many on 
the Microsoft
Newsgroups and at Woody's Lounge
Remember to Refresh your page. [F5] in Internet Explorer. [Ctrl+R] in Netscape and Firefox.
This page last revised: 25 Oct 2023 22:40:53 -0400 .
This is more complicated than you might imagine, primarily because of the way our calendar is constructed. Before your head starts spinning, realize that the hard work has been done for you. Paul Edstein has posted a document with alternative date fields with calculations on the Windows Secrets Word Forum. See also Greg Maxey's page on Calculated Dates.
Because it is very difficult to keep track of where fields begin and end, I've tried to use colored text and highlighting in the nested fields to show which field is where. This coloring is not in the Word documents and could end up coloring your result text if you were to use it. In tracing a field, look for a colored or highlighted opening field delimiter { and then look to the right to see the next closing delimiter } of the same color and highlighting. Everything in between those two delimiters is in that field.
Here is an example of a field written in response to the request:
I need to create a fiscal year field in Word 97 that accomplishes in Word what the following statement accomplished in English:
If today's month and day are less than or equal to June 30, display the current year, else display current year + 1.
{ IF { DATE \@ "MM" } < 07 { DATE \@ "yyyy" } { = { DATE \@ "yyyy" }+1 \# "0000" } }
If today were January 31, 2015, The following expression using that field would display: "June 30, 2015" where the year is the field and the rest of the date is regular typed text. If today were July 1, 2015, the same expression would display as "June 30, 2016." Please note that the field above only produces the year; you'll need to put in the rest of the date on your own. You probably want to be using the CREATEDATE field instead of the date field, as well. For more on this, see Using Date Fields in Microsoft Word.
Remember that you must have Word insert the {braces}. Use (Ctrl+F9) when writing fields; typing them won't work.
Here's a head-spinner!
What follows is a field that gives you the date two weeks
from the day a document is created, in regular written English format:
Creation date: July 9, 2015 - desired
field result = July 23, 2015
Creation date: July 23, 2014 - desired field result = August 6, 2014
Creation date: December 20, 2014 - desired field result = January 3, 2015
Creation date: February 27, 2015 - desired field result = March 12, 2015
Creation date: February 27, 2016 - desired field date result = March 13, 2016
{
QUOTE "{ SET
" Delay" "14"
}
{ SET "DaysInMonth" {
IF { CreateDate \@ "MM" }
<> 2
{ = ROUND(30.575*{
CreateDate \@ "MM" },0)-
ROUND(30.575*{ = {
CreateDate \@
"MM" }
-1 },0)
}
{ IF
{ = MOD(
{
CreateDate \@"yy" }
, 4 ) } > 0 "28" "29" } }
}
{ SET "NextMonth"
{
IF {
CreateDate \@ "MM" }
= 12 "1/97"
"{ =
{ CreateDate \@ "MM" }
+ 1 }/97 } }
{ IF
{
= { REF "Delay" } +
{ CreateDate \@ "dd" } }
<= { REF"DaysInMonth" }
{
CreateDate \@ "MMMM { =
{ REF "Delay"
} +
{
CreateDate \@ "dd" } }, yyyy"}{
QUOTE { NextMonth \@ "MMMM" }
{ = { REF "Delay" } +
{ CreateDate \@ "dd" } -
{ REF"DaysInMonth" }
},
{ IF {
CreateDate \@ "MM" }
<> 12 { CreateDate \@ "yyyy" }
{ CreateDate \@ "{
= 1 + {
CreateDate \@
"yyyy" } \#
"xxxx" }"
}
} }
}" }
The colors in the sample field above represent my attempt to match field delimiters and show nesting of fields. Line breaks have been added for clarity and should not be a part of the actual field. I added these modifications help illustrate what the field looks like. Those fields shown in green contain no nested fields but may contain references to bookmark values set by other fields. Do not try to copy the field from this page; instead you can download a document containing the actual field if you want.
|
Paul Edstein has posted a document with alternative date fields with calculations on the Windows Secrets Word Forum. You can download it here.
A (possibly earlier version) backup is on Graham Mayor's site: You are looking for DateCalc.zip by Paul Edstein. I regularly start with this document's fields when I need to make such a field. Again, I would never start from scratch.
A: Yes. Pick your bookmark name for the ASK field and generate that field. Then generate the complex date field using Woodman's Add-In. Then Alt+F9 to reveal field codes and, within your field, replace CreateDate with the name of your bookmark. Press AltF9 again to hide the codes. What follows is such combination of an ASK field together with a modified field giving a date 7 days before the date given in response to the ASK prompt. These are suitable for use in a mailmerge.
{ ASK MyDate "What is the target date?" \d "December 24, 2015" }
{ QUOTE { SET Delay "7" }{ SET "NextPrevMonth" { IF { MYDATE \@ "MM" } = 1 "12/97" "{ = { MYDATE \@ "MM" } - 1 }/97" } }
{ SET "DaysInMonth" { IF { NextPrevMonth \@ "MM" } <> 2 { = INT(30575*{ NextPrevMonth \@ "MM" }/1000+1/2)-INT(30575*{ = { NextPrevMonth \@ "MM" } - 1 }/1000+1/2) }{ IF { = INT({ MYDATE \@"yy" }/4 ) } = { = { MYDATE \@"yy" }/4 }"29" "28" } } }
{ IF
{ = { MYDATE \@ "dd" } - { REF Delay } } > 0
{ QUOTE { MYDATE \@ "MMMM" }" "{ = { MYDATE \@ "dd" } - { REF Delay \* MERGEFORMAT } \# "0" }", "{ MYDATE \@ "yyyy" }"" }
{ QUOTE { NextPrevMonth \@ "MMMM" }" "{ = { MYDATE \@ "dd" } - { REF Delay \* MERGEFORMAT } + { DaysInMonth } \# "0" }
", "{ IF { MYDATE \@ "MM" } <> 1 { MYDATE \@ "yyyy" }{ = { MYDATE \@ "yyyy" } - 1 \# "xxxx" } }"" }
} }
You can also keep such fields as AutoText entries in a global template so that you only have to create them once. See my AutoText / AutoCorrect chapter for more on saving fields as AutoText.
So much for fields.
Calculated dates are easiest performed by a short macro -
Selection.InsertBefore Format((Date + 14), "d MMMM yyyy")
will enter the current date + 14 at the cursor.
A {CREATEDATE \@ "d MMMM yyyy"} field will set the current date in the same format.
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'
Selection.GoTo What:=wdGoToBookmark, Name:="DueDate" Selection.InsertBefore Format((Date + 14), "d MMMM yyyy")
(for instructions on how to insert a macro, see Macros and VBA)
(for more on date formatting, see Using Date Fields in Microsoft Word)
--
For a flexible alternative, here is a macro that prompts for the number of days and can add or subtract days:
Sub InsertFutureDate()
' 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
Dim Title As String
Dim Default As String
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 = "60" ' Set default.
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)
'
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-- \ \\ // / ( @ @ ) ----oOOO----(_)----OOOo----- <>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
Graham Mayor http://www.gmayor.com/Word_pages.htm
Graham has produced an Add-In for this. Insert a Future (or Past) Date Add-In
(for instructions on how to insert a macro, see Macros and VBA)
(for more on date formatting, see Using Date Fields in Microsoft Word)
You may want to look at:
www.mvps.org/word/FAQs/MacrosVBA/DateOfPrevMonth.htm
by three of the Word MVPs for more on calculated dates in VBA.
Rob asked:
I have created a template that requires a date from to date to field. e.g. 12/1/00 to 12/7/00. Basically I want it to be the weeks beginning date to the weeks ending date. I want this data automatically filled each time the user creates a new document using the template.
Does anyone have any ideas on how to do this?
Thanks
Rob
------------
Answer 1
You need to add two things to your template - a bookmark called Date (you can call it anything you like, but you'll have to change the reference in the code) and a macro called AutoNew which runs when you create a new document based on the template. It seeks out your bookmark and writes the date in the format at that location. Here it adds 90 days, but you can change the 90 to anything else you like.
11/8/14 to 9/11/15
Sub AutoNew() Selection.GoTo What:=wdGoToBookmark, Name:="Date" Selection.InsertBefore Format((Date), "d/m/yy") Selection.InsertAfter " to " Selection.InsertAfter Format((Date + 90), "d/m/yy") End Sub
--
\ \\ // /
( @ @ )
----oOOO----(_)----OOOo-----
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
Graham Mayor <gmayor@mvps.org>
The five ages of man:
Lager.. Aga .. Viagra .. Saga .. Gaga
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>
-----------
Answer 2
I think Robert is wanting to return the date the week started on and the date
the week will end on, like from Sunday to Saturday. However I just looked at
the example dates. They are Friday to Thursday - so now I really don't know.
<grin>
Anyway Robert, if you are wanting a week start date then a function is needed to determine the weekday and then subtract the appropriate number of days from the current date. I modified Graham's macro slightly to include the result of the function and changed the name of the bookmark.
For the function I used Sunday as the starting date and Saturday as the ending date. Should you want to use other days of the week then it's just a matter of changing the current date calculation for the Case. Case 1 is Sunday, Case 7 is Saturday. So for example if you wanted the starting date to be Monday, then Case 1 would be WeekStart = Date - 6, Case 2 would be WeekStart = Date, etc.
Function WeekStart() As Date Dim wday As Byte wday = WeekDay(Date) Select Case wday Case 1 WeekStart = Date Case 2 WeekStart = Date - 1 Case 3 WeekStart = Date - 2 Case 4 WeekStart = Date - 3 Case 5 WeekStart = Date - 4 Case 6 WeekStart = Date - 5 Case 7 WeekStart = Date - 6 End Select End Function Sub AutoNew() Selection.GoTo What:=wdGoToBookmark, Name:="wDate" Selection.InsertBefore Format(WeekStart(), "d/m/yy") Selection.InsertAfter " to " Selection.InsertAfter Format(WeekStart() + 7, "d/m/yy") End Sub
~~~~~~~~~~~
Hope this helps,
Beth Melton, Microsoft Office MVP
See also: How do I return the date of the previous month using VBA? http://word.mvps.org/FAQs/MacrosVBA/DateOfPrevMonth.htm
Greg Maxey posted some powerful vba code in this thread to use Date Picker Content Controls. It is designed to not count certain holidays and/or weekends in calculations. The variations are set using the tags in the result content control. Some of it is incorporated in this macro-enabled template which you could download.
The up-to-date version of this FAQ may be found at:
Changes / suggestions / ideas can be sent to Charles Kenyon.
Copyright © 2000, 2001, 2013, 2015 Charles Kyle Kenyon, Madison, Wisconsin, USA, all rights reserved.
Page
views since 13 April 2004
