Calculate a due date in Word 2000

[BSimpson]

I'm trying to do the following in a Word merge: I'm trying to calculate a
due-by date for court filings by having Word subtract 7 days from a court
hearing date entered through a merge prompt. The hearing date is from a
list of specific dates published by each of 3 different courts.

The merge prompts for a hearing date through a {fillin} field
I need to find how to have the merge itself calculate a due-by date that is
7 calendar days *prior* to the hearing date (it doesn't have to account for
weekends, holidays, or anything like that).

For instance, in response to the merge's prompt, I enter a hearing date of
7-31-03. I want to have the merge output the date of 7-24-03 with the
formatting being MMMM d, YYYY. I already know how to format the date output
with the \@ switch.

So, the output would be: "The hearing date is June 31, 2003, and the due-by
date is June 24, 2003."

Just for test purposes I threw the following together (obviously, {}will
actually be merge fields)
{set testdate{fillin "Enter the Hearing date."}}
{set duedate{ =({testdate} - 7)}}

The hearing date is: {testdate \@ "MMMM d, YYYY"}
The due date is: {duedate \@ "MMMM d, YYYY"}

entering this into Word with actual field codes and running the little
routine correctly prompts for the hearing date, but the due date displays
as -34.

I'm almost **certain** I've seen how to do this SOMEWHERE before, but I just
can't remember where or what the field coding was!! Is it maybe something
involving converting the input date to a serial-date format or something????

To be sure, I *could* just have the user enter the hearing date and then
also prompt for the due-by date to be entered, but I'd rather cut down on
the entry of extra data if possible! I *can't* go the opposite route,
having the user enter the due-by date and then just adding 7 days, as the
hearing dates are *very* specific.

I need to do this from within Word itself. Is there maybe a freebie add-in
or something???

Help???

Mucho-mega-thanks!
Brian

 

[BSimpson]

Neither of these will work. It *must* be a mailmerge - this little bit of coding is to go into an existing document that's been modified for new requirements from the local Court system. This *will* be used in a whole group of merge documents that I use constantly, and almost always for a whole batch of cases (read: merge records).

Currently, the merge prompts for the hearing date for the case through a {fillin} popup - this works just perfectly. The new requirement is that elsewhere in the document a "duedate" is now required. The hearing date *must* be entered for each case - it comes from a list of dates specific to each Judge that changes from month to month. The "duedate" only has to be 7 calendar days before the hearing date. It does not have to avoid weekends or holidays in the calculation, just simple math performed with calendar dates.

The goal of this is to produce the following: {hearingdate} -7 = {duedate}

I've tried the following:
------------begin sample--------
{set hearingdate{fillin "Enter the Hearing date in mm-dd-yy format, then click [OK]."}}
{set duedate{ =DateAdd("d", -7, hearingdate)}}

The hearing date is: {hearingdate \@ "MMMM d, YYYY" }
The due date is: {duedate \@ "MMMM d, YYYY" }
-----------end sample----------------

The end results are, predictably, not what I was hoping for. Using 7-31-03 as the hearing date *should* produce a due date of 7-24-03. What I get with the above is:
The hearing date is: July 31, 2003 <-- this is correct
The due date is: !Syntax Error, ( obviously not the desired result.

I know it's possible to do the same sort of thing in Excel with a very simple =sum formula, but tinkering around with the =dateadd function only gives me syntax errors or unexpected end of formula errors. I came up with something at one point (alas, didn't save it...) that *seemed* to sort of work - it produced a due date of -34. According to the DateAdd info I've found, it *is* possible to use a negative number to produce a date in the past, relative to the hearing date field.

Help????????

 

[Dave Lett]

Hi Brian,

Well, this _is_ a vba group, so I'm guessing that you can use VBA to reach
your due date. In that case, you can use the DateAdd function. For example

Dim dDue As Date
Dim dHearing As Date
dHearing = Now
dDue = DateAdd("w", -7, dHearing)
MsgBox dDue


All you have to do is get the date from the fill-in field and then write the
calculated date for each merge record to the correct location.

HTH

Neither of these will work. It *must* be a mailmerge - this little bit of
coding is to go into an existing document that's been modified for new
requirements from the local Court system. This *will* be used in a whole
group of merge documents that I use constantly, and almost always for a
whole batch of cases (read: merge records).

Currently, the merge prompts for the hearing date for the case through a
{fillin} popup - this works just perfectly. The new requirement is that
elsewhere in the document a "duedate" is now required. The hearing date
*must* be entered for each case - it comes from a list of dates specific to
each Judge that changes from month to month. The "duedate" only has to be 7
calendar days before the hearing date. It does not have to avoid weekends
or holidays in the calculation, just simple math performed with calendar
dates.

The goal of this is to produce the following: {hearingdate} -7 = {duedate}

I've tried the following:
------------begin sample--------
{set hearingdate{fillin "Enter the Hearing date in mm-dd-yy format, then
click [OK]."}}
{set duedate{ =DateAdd("d", -7, hearingdate)}}

The hearing date is: {hearingdate \@ "MMMM d, YYYY" }
The due date is: {duedate \@ "MMMM d, YYYY" }
-----------end sample----------------

The end results are, predictably, not what I was hoping for. Using 7-31-03
as the hearing date *should* produce a due date of 7-24-03. What I get with
the above is:
The hearing date is: July 31, 2003 <-- this is correct
The due date is: !Syntax Error, ( obviously not the desired result.

I know it's possible to do the same sort of thing in Excel with a very
simple =sum formula, but tinkering around with the =dateadd function only
gives me syntax errors or unexpected end of formula errors. I came up with
something at one point (alas, didn't save it...) that *seemed* to sort of
work - it produced a due date of -34. According to the DateAdd info I've
found, it *is* possible to use a negative number to produce a date in the
past, relative to the hearing date field.

Help????????

 

[Charles Kenyon]

The following works in a mailmerge and was generated using Woodman's Add-In
as described in my last post:

{ ASK MyDate "What is the court date? \o }
{ 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 } \#
"0" }", "{ MYDATE \@ "yyyy" }"" }
{ QUOTE { NextPrevMonth \@ "MMMM" }" "{ = { MYDATE \@ "dd" } - { REF Delay }
+ { DaysInMonth } \# "0"}", "{ IF { MYDATE \@ "MM" } <> 1 { MYDATE \@
"yyyy" }{ = { MYDATE \@ "yyyy" } - 1 \# "xxxx" } }"" }
}
}

I would suggest using the Add-In and the procedure described in that post
rather than trying to insert this by hand.
--
Charles Kenyon
Word New User FAQ & Web Directory:
<URL: http://www.addbalance.com/word/index.htm>
Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide)
<URL: http://www.addbalance.com/usersguide/index.htm>
Word Resources Page
<URL: http://www.addbalance.com/word/wordwebresources.htm>
See also the MVP FAQ: <URL: http://www.mvps.org/word/> which is awesome!
--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

Neither of these will work. It *must* be a mailmerge - this little bit of
coding is to go into an existing document that's been modified for new
requirements from the local Court system. This *will* be used in a whole
group of merge documents that I use constantly, and almost always for a
whole batch of cases (read: merge records).

Currently, the merge prompts for the hearing date for the case through a
{fillin} popup - this works just perfectly. The new requirement is that
elsewhere in the document a "duedate" is now required. The hearing date
*must* be entered for each case - it comes from a list of dates specific to
each Judge that changes from month to month. The "duedate" only has to be 7
calendar days before the hearing date. It does not have to avoid weekends
or holidays in the calculation, just simple math performed with calendar
dates.

The goal of this is to produce the following: {hearingdate} -7 = {duedate}

I've tried the following:
------------begin sample--------
{set hearingdate{fillin "Enter the Hearing date in mm-dd-yy format, then
click [OK]."}}
{set duedate{ =DateAdd("d", -7, hearingdate)}}

The hearing date is: {hearingdate \@ "MMMM d, YYYY" }
The due date is: {duedate \@ "MMMM d, YYYY" }
-----------end sample----------------

The end results are, predictably, not what I was hoping for. Using 7-31-03
as the hearing date *should* produce a due date of 7-24-03. What I get with
the above is:
The hearing date is: July 31, 2003 <-- this is correct
The due date is: !Syntax Error, ( obviously not the desired result.

I know it's possible to do the same sort of thing in Excel with a very
simple =sum formula, but tinkering around with the =dateadd function only
gives me syntax errors or unexpected end of formula errors. I came up with
something at one point (alas, didn't save it...) that *seemed* to sort of
work - it produced a due date of -34. According to the DateAdd info I've
found, it *is* possible to use a negative number to produce a date in the
past, relative to the hearing date field.

Help????????