Adding dynamic DateDiff field to document

A

ajkessel

Cross-posted from microsoft.public.word after a week with no responses:

Using Word 2002, is there any way to add a field that gives the
remaining days to a certain date? It does not appear that DateDiff()
is a valid function for a formula in an inserted field. Right now, I
have a very short VBA macro that does this:

Selection.InsertAfter " -" & DateDiff("d", Now(), Selection)

The problem is that I have to highlight the date and re-execute the
macro every day to update the remaining days. What I want is to insert
a calculated field such that it always shows the number of days between
a given date and now(). I will have several of such fields throughout
the document, and it would be nice if it were easy to add additional
dates on an ongoing basis.

Is there any way to do this?
 
M

macropod

Hi,

You can do this simply enough with some field coding:
{=
{SET a{=INT((14-{EndDate \@ M})/12)}}
{SET b{={EndDate \@ yyyy}+4800-a}}
{SET c{={EndDate \@ M}+12*a-3}}
{SET d{EndDate \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
-
{SET a{=INT((14-{Date \@ M})/12)}}
{SET b{={Date \@ yyyy}+4800-a}}
{SET c{={Date \@ M}+12*a-3}}
{SET d{Date \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
\# ,0}

To save you all the effort of coding this yourself, simply download my Date Calc 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
and copy the field under the heading 'Calculate the # Days Difference Between Two Dates'. Then press Alt-F9 to expose the field
coding and replace each instance of 'StartDate' with 'Date'. If you're using this in a protected form, you'll need to make sure your
formfield sets a bookmark named 'EndDate' and is set to 'calculate on exit'.

If you're not using a protected form, you'll need to insert a field coded as:
{SET EndDate "30 June 2007"}
after the '=' sign, using Ctrl-F9 to create the field braces. Change the date to whatever date you want.

Cheers

--
macropod
[MVP - Microsoft Word]


| Cross-posted from microsoft.public.word after a week with no responses:
|
| Using Word 2002, is there any way to add a field that gives the
| remaining days to a certain date? It does not appear that DateDiff()
| is a valid function for a formula in an inserted field. Right now, I
| have a very short VBA macro that does this:
|
| Selection.InsertAfter " -" & DateDiff("d", Now(), Selection)
|
| The problem is that I have to highlight the date and re-execute the
| macro every day to update the remaining days. What I want is to insert
| a calculated field such that it always shows the number of days between
| a given date and now(). I will have several of such fields throughout
| the document, and it would be nice if it were easy to add additional
| dates on an ongoing basis.
|
| Is there any way to do this?
|
 
A

ajkessel

macropod said:
You can do this simply enough with some field coding:
{=
{SET a{=INT((14-{EndDate \@ M})/12)}}
{SET b{={EndDate \@ yyyy}+4800-a}}
{SET c{={EndDate \@ M}+12*a-3}}
{SET d{EndDate \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
-
{SET a{=INT((14-{Date \@ M})/12)}}
{SET b{={Date \@ yyyy}+4800-a}}
{SET c{={Date \@ M}+12*a-3}}
{SET d{Date \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
\# ,0}

To save you all the effort of coding this yourself, simply download my Date Calc 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
and copy the field under the heading 'Calculate the # Days Difference Between Two Dates'. Then press Alt-F9 to expose the field
coding and replace each instance of 'StartDate' with 'Date'. If you're using this in a protected form, you'll need to make sure your
formfield sets a bookmark named 'EndDate' and is set to 'calculate on exit'.
Click to expand...

Thanks, that's almost exactly what I was looking for. What I'd like to
do is be able to insert this formula with a macro that prompts the user
for "EndDate." I tried to copy your code into VBA with a
Selection.InsertFormula command, but it gives me an error that the
string is longer than 255 characters. Also, even with a short string,
it seems to be inserting the text as literal rather than an actual
formula.

It seems what I need is to create VBA to do nested fields. But this has
proved quite complicated. Any easy way to do this?
 
A

ajkessel

Actually, I sort of figured it out, although it's terribly awkward.
Here's what I got -- it seems to do the trick:

Sub deadline()
Target = InputBox("Enter deadline", "Deadline")
If Not IsDate(Target) Then
MsgBox ("Sorry, that is not a valid date.")
Else
Selection.Font.Bold = 1

Selection.InsertAfter "(" + Target + " -"
Selection.Collapse direction:=wdCollapseEnd
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="="
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET EndDate """ + Target + """"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeParagraph
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET a"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="=INT((14-"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="EndDate \@ M"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:=")/12)"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeParagraph
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET b"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="="
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="EndDate \@ yyyy"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:="+4800-a"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeParagraph
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET c"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="="
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="EndDate \@ M"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:="+12*a-3"
Selection.MoveRight Unit:=wdCharacter, Count:=5
Selection.TypeParagraph
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET d"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="EndDate \@ d"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeParagraph
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:= _
"=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-320"
Selection.TypeText Text:="45"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeParagraph
Selection.TypeText Text:="-"
Selection.TypeParagraph
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET a"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="=INT((14-"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="Date \@ M"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:=")/12)"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeParagraph
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET b"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="="
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="Date \@ yyyy"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:="+4800-a"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeParagraph
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET c"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="="
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="Date \@ M"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:="+12*a-3"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeParagraph
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET d"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="Date \@ d"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeParagraph
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:= _
"=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-320"
Selection.TypeText Text:="45"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeParagraph
Selection.TypeText Text:="\# ,0"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.InsertAfter ")"
Selection.Collapse direction:=wdCollapseEnd
ActiveDocument.Fields.Update
Selection.Font.Bold = 0
End If
End Sub
 
A

ajkessel

In case someone is actually trying to use this in the distant future,
there were a couple of formatting glitches in the macro above depending
on the context. Here's a fixed version:

Sub Deadline()
Target = InputBox("Enter deadline", "Deadline")
If Not IsDate(Target) Then
MsgBox ("Sorry, that is not a valid date.")
Else
Selection.Collapse direction:=wdCollapseStart
Selection.InsertAfter " (" + Target + " -"
Selection.Font.Bold = 1
Selection.Collapse direction:=wdCollapseEnd
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="="
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET EndDate """ + Target + """"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Chr(11)

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET a"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="=INT((14-"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="EndDate \@ M"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:=")/12)"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeText Chr(11)
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET b"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="="
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="EndDate \@ yyyy"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:="+4800-a"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeText Chr(11)
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET c"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="="
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="EndDate \@ M"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:="+12*a-3"
Selection.MoveRight Unit:=wdCharacter, Count:=5
Selection.TypeText Chr(11)
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET d"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="EndDate \@ d"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeText Chr(11)
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:= _
"=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-320"
Selection.TypeText Text:="45"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Chr(11)
Selection.TypeText Text:="-"
Selection.TypeText Chr(11)
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET a"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="=INT((14-"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="Date \@ M"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:=")/12)"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeText Chr(11)
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET b"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="="
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="Date \@ yyyy"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:="+4800-a"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeText Chr(11)
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET c"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="="
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="Date \@ M"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Text:="+12*a-3"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeText Chr(11)
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="SET d"
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:="Date \@ d"
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeText Chr(11)
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
PreserveFormatting:=False
Selection.TypeText Text:= _
"=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-320"
Selection.TypeText Text:="45"
Selection.MoveRight Unit:=wdCharacter, Count:=2
Selection.TypeText Chr(11)
Selection.TypeText Text:="\# ,0"
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.InsertAfter ")"
Selection.Collapse direction:=wdCollapseEnd
ActiveDocument.Fields.Update
Selection.Font.Bold = 0
End If
End Sub
 
M

macropod

Hi ajkessel,

There really isn't any need to try to code the field into a macro. All you need to do is to insert the field in the document where
you want the result to appear. Setting the EndDate can also be done via a formfield in a protected document, or an ASK field or a
FILLIN field. If you're going to use a macro, you may as well stick with DateDiff.

There's some code for converting field codes to stings and back again here:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=320421

Cheers

--
macropod
[MVP - Microsoft Word]


ajkessel said:
You can do this simply enough with some field coding:
{=
{SET a{=INT((14-{EndDate \@ M})/12)}}
{SET b{={EndDate \@ yyyy}+4800-a}}
{SET c{={EndDate \@ M}+12*a-3}}
{SET d{EndDate \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
-
{SET a{=INT((14-{Date \@ M})/12)}}
{SET b{={Date \@ yyyy}+4800-a}}
{SET c{={Date \@ M}+12*a-3}}
{SET d{Date \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
\# ,0}

To save you all the effort of coding this yourself, simply download my Date Calc 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
and copy the field under the heading 'Calculate the # Days Difference Between Two Dates'. Then press Alt-F9 to expose the field
coding and replace each instance of 'StartDate' with 'Date'. If you're using this in a protected form, you'll need to make sure your
formfield sets a bookmark named 'EndDate' and is set to 'calculate on exit'.
Click to expand...

Thanks, that's almost exactly what I was looking for. What I'd like to
do is be able to insert this formula with a macro that prompts the user
for "EndDate." I tried to copy your code into VBA with a
Selection.InsertFormula command, but it gives me an error that the
string is longer than 255 characters. Also, even with a short string,
it seems to be inserting the text as literal rather than an actual
formula.

It seems what I need is to create VBA to do nested fields. But this has
proved quite complicated. Any easy way to do this?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding dynamic DateDiff field to document 0
DateDiff Function 3
calling a function in word document dynamically. 2
DateDiff & 30 days 7
Business Days / Work Days only / Date Math 1
File save with date 0
Search and replace involving a field 8
Printing modified CreateDate field in header reverts back - Word 2 6

Top