How Can I Put Calculated Dates in My Document Using Word?

ADD Balance logo - trademark - Microsoft Word help frequently asked questions

Microsoft Word FAQ
(frequently asked questions)

Supplement

 on ADD Balance by Charles Kenyon

Thank you for the suggestions. ("Smilies" from Woody's Lounge.) Click to go to Lounge. with input and suggestions from many on Thank you for the suggestions. ("Smilies" from Woody's Lounge.) Click to go to Lounge.
the Microsoft Newsgroups and at Woody's Lounge 

Remember to Refresh your page. [F5] in Internet Explorer. [Ctrl+R] in Netscape and Firefox.

Back Up Next

 

Home Word Tutorial - Intermediate Legal Users Guide to Word Downloads Books about Microsoft Word - Newsgroup suggestions Links Web Resources for Microsoft Word

 

 

 

This page last revised: 01 Oct 2014 10:27:37 -0500 .

Q: How Can I Put Calculated Dates in Word?

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.

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, 2001, The following expression using that field would display: "June 30, 2001" where the year is the field and the rest of the date is regular typed text. If today were July 1, 2001, the same expression would display as  "June 30, 2002." 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! Dizzy! This little head-spinner emoticon borrowed from Woody's Word Lounge. Click to go there. 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, 2001 - desired field result = July 23, 2001
Creation date: July 23, 2001 - desired field result = August 6, 2001
Creation date: December 20, 2001 - desired field result = January 3, 2002
Creation date: February 27, 2003 - desired field result = March 12, 2003
Creation date: February 27, 2004 - desired field date result = March 11, 2004

{ 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. I hope it helps. Those fields shown in green contain no nested fields but may contain references to bookmark values set by other fields. Line breaks have been added for clarity and should not be a part of the actual field.  You can download a document containing the actual field if you want.

 

The field shown above was produced by a macro written by Chris Woodman as an Add-In. It allows insertion of such a calculated date field (up to 28 days before or after the current date) field into a Word template in Word 6/7 97/2000/2002. It is easy to use and works well. I sure wouldn't want to try constructing these fields by hand! Unfortunately, his site has been down for some time and you can no longer download his Add-In. I do not have permission to distribute it.

http://www.chriswoodman.co.uk/Delayed Dates.htm

Fortunately, though, Paul Edstein's work has both supplanted and surpassed Chris Woodman's in many ways.

 

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.

 

Q: Can I use a date other than one of the date fields? i.e. a date that is in response to an ASK prompt? 

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. 

Now for a discussion of VBA:

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

(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/00 to 9/11/00

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  


Return to Questions List
Return to Supplemental Questions List (not yet implemented)

Return to Dates

 The up-to-date version of this FAQ may be found at:

http://www.addbalance.com/word/

bSend e-mail

Changes / suggestions / ideas can be sent to Charles Kenyon

Microsoft Word FAQ - Frequently Asked Questions - Kenyon

Copyright © 2000, 2001, 2013 Charles Kyle Kenyon, Madison, Wisconsin, USA, all rights reserved.

Hit Counter Page views since 13 April 2004