How do insert an automatic future date in Word?

Discussion:

(too old to reply)

spartacus3

2007-02-24 01:52:00 UTC

In Word, I need to create a field that inserts a future date two business
days ahead of the current date. I suspect it involves { DATE \@ "M/d/yyyy" }
and +2 somehow. I'm still not sure how to make it skip weekends and holidays.
Does anybody have any ideas?

Jezebel

2007-02-24 02:42:38 UTC

Permalink

Unfortunately, Word doesn't provide any direct way to do date arithmetic
within fields. Another regular poster has a field-based method which, for
reasons we shan't revisit, I think it would be irresponsible to use in a
commercial context. And in anycase I don't know that the method can deal
with weekends, let alone holidays (whose holidays?).

The VBA method is to write an AutoNew macro that calculates the value and
stores it as a document property or document variable. To display the value,
use a DocProperty or DocVariable field.

Post by spartacus3
In Word, I need to create a field that inserts a future date two business
and +2 somehow. I'm still not sure how to make it skip weekends and holidays.
Does anybody have any ideas?

Peter Jamieson

2007-02-24 10:09:56 UTC

Permalink

There's no really simple way to do any of what you want using fields.

For the simple case (i.e. forgetting about the weekends and holidays thing)
you can try using a DATABASE field, but you need a Jet database to do it
e.g. in Word 2002/2003

{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,datevalue('{ DATE
\@"YYYY-MM-DD" }'))" \@"M/D/YYYY" }

or even

{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
\@"YYYY-MM-DD" }')" \@"M/D/YYYY" }

where /all/ the {} are the special field braces you can insert using
ctrl-F9.

For earlier versions of Word you need a bit more to prevent Word trying to
open Access (which you may not have).

You may also encounter a number of security-related barriers to this
approach.

The .mdb needs to be a valid Jet database, but it can be empty, i.e. have no
visible queries or tables. There are various ways to create one:
a. If you have Access, it should be easy to create an empty .mdb.
b. Depending on what software is on your system you may be able to
right-click in Windows Explorer and use the "New" option to create a new
"Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it to your
system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to start the Mail
Merge Wizard, follow the task panes, and in "Select Recipients", select
"Type a new list", then click Create. Add a record, then save the resulting
.mdb

Whether it's advisable to use this approach is a different question. The
main drawback of is its reliance on an external file (and related database
access software) and the increased potential for maintenance problems.

To deal with weekends you could use a more complicated expression, e.g.
something like

{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
\@"YYYY-MM-DD" }'),1),2,2,2,2,4,4,3),datevalue('{ DATE \@"YYYY-MM-DD" }'))"
\@"M/D/YYYY" }

or, paring it down a bit, perhaps

{ SET d "{ DATE \@"YYYY-MM-DD" }" }{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday('{ d }'),2,2,2,2,4,4,3),'{ d }')" \@"M/D/YYYY" }

NB in recent versions of Word, the result of a DATABASE field sometimes
includes a paragraph mark, which has rendered it useless for including
individual text values. If the result is a date or a number, a date/numeric
format switch seems to deal with that problem.

As for the holidays, even assuming you are only interested in one country's
holidays, you would only be able to build additional query criteria in if
the list of holidays was atypically small and predictable (partly because
the query text length is constrained, probably to 255 characters).
Typically, you will simply have to maintain a calendar of some kind. If you
also keep that in a database (somewhere, depends on what sort of system
we're talking about) then you have to decide how far ahead your calendar is
going to go, and precisely how you are going to represent holidays, weekends
and so on. If you are only ever going to have a "two business days ahead"
requirement, you might as well have a database with two columns containing
the precise result you want:

currentdate,dateplus2
2007-02-24,2007-02-27

Then you would need

{ DATABASE \d "your database path name" \s "SELECT dateplus2 FROM <whatever>
WHERE currentdate = '{ DATE \@"YYYY-MM-DD" }'" \@"M/D/YYYY" }

You could even use a Word document to contain such a database if it weren't
for the fact that Word doesn't consider the result to be a date, so doesn't
apply the date format, so may insert that extra paragraph I mentioned.

Peter Jamieson

Graham Mayor

2007-02-24 11:04:33 UTC

Permalink

No 'really simple' way, but it is possible. If you want to see the method
Jezebel decries and make up your own mind, see
www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902 , however he is
correct that the holidays issue will be a problem with a field solution..
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and holidays
thing) you can try using a DATABASE field, but you need a Jet
database to do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,datevalue('{ DATE
or even
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert using
ctrl-F9.
For earlier versions of Word you need a bit more to prevent Word
trying to open Access (which you may not have).
You may also encounter a number of security-related barriers to this
approach.
The .mdb needs to be a valid Jet database, but it can be empty, i.e.
have no visible queries or tables. There are various ways to create
one: a. If you have Access, it should be easy to create an empty .mdb.
b. Depending on what software is on your system you may be able to
right-click in Windows Explorer and use the "New" option to create a
new "Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it to
your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to start
the Mail Merge Wizard, follow the task panes, and in "Select
Recipients", select "Type a new list", then click Create. Add a
record, then save the resulting .mdb
Whether it's advisable to use this approach is a different question.
The main drawback of is its reliance on an external file (and related
database access software) and the increased potential for maintenance
problems.
To deal with weekends you could use a more complicated expression,
e.g. something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
"SELECT dateadd('d',choose(weekday('{ d }'),2,2,2,2,4,4,3),'{ d }')"
NB in recent versions of Word, the result of a DATABASE field
sometimes includes a paragraph mark, which has rendered it useless
for including individual text values. If the result is a date or a
number, a date/numeric format switch seems to deal with that problem.
As for the holidays, even assuming you are only interested in one
country's holidays, you would only be able to build additional query
criteria in if the list of holidays was atypically small and
predictable (partly because the query text length is constrained,
probably to 255 characters). Typically, you will simply have to
maintain a calendar of some kind. If you also keep that in a database
(somewhere, depends on what sort of system we're talking about) then
you have to decide how far ahead your calendar is going to go, and
precisely how you are going to represent holidays, weekends and so
on. If you are only ever going to have a "two business days ahead"
requirement, you might as well have a database with two columns
currentdate,dateplus2
2007-02-24,2007-02-27
Then you would need
{ DATABASE \d "your database path name" \s "SELECT dateplus2 FROM
You could even use a Word document to contain such a database if it
weren't for the fact that Word doesn't consider the result to be a
date, so doesn't apply the date format, so may insert that extra
paragraph I mentioned.
Peter Jamieson

Post by spartacus3
In Word, I need to create a field that inserts a future date two
business days ahead of the current date. I suspect it involves {
and +2 somehow. I'm still not sure how to make it skip weekends and holidays.
Does anybody have any ideas?

macropod

2007-02-26 07:44:59 UTC

Permalink

Hi Peter/Graham,

Modifying one of my date calculation fields at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
to cope with weekends would be fairly trivial. For example, adding:
{SET jd{=jd+(MOD(jd,7)>4)*(4-MOD(jd,7))}}
or
{SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
after the existing 'SET' jd field would adjust the calculated dates to the previous Friday or next Monday, respectively.

Dealing with public holidays could be problematic if they fall on Mondays or Fridays, but only because they'd have to be coded for
on a case-by-case basis. VBA probably wouldn't be any easier in this regard.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and holidays thing) you can try using a DATABASE field, but you need a Jet
database to do it e.g. in Word 2002/2003
or even
where /all/ the {} are the special field braces you can insert using ctrl-F9.
For earlier versions of Word you need a bit more to prevent Word trying to open Access (which you may not have).
You may also encounter a number of security-related barriers to this approach.
The .mdb needs to be a valid Jet database, but it can be empty, i.e. have no visible queries or tables. There are various ways to
a. If you have Access, it should be easy to create an empty .mdb.
b. Depending on what software is on your system you may be able to right-click in Windows Explorer and use the "New" option to
create a new "Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to start the Mail Merge Wizard, follow the task panes, and in "Select
Recipients", select "Type a new list", then click Create. Add a record, then save the resulting .mdb
Whether it's advisable to use this approach is a different question. The main drawback of is its reliance on an external file (and
related database access software) and the increased potential for maintenance problems.
To deal with weekends you could use a more complicated expression, e.g. something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
NB in recent versions of Word, the result of a DATABASE field sometimes includes a paragraph mark, which has rendered it useless
for including individual text values. If the result is a date or a number, a date/numeric format switch seems to deal with that
problem.
As for the holidays, even assuming you are only interested in one country's holidays, you would only be able to build additional
query criteria in if the list of holidays was atypically small and predictable (partly because the query text length is
constrained, probably to 255 characters). Typically, you will simply have to maintain a calendar of some kind. If you also keep
that in a database (somewhere, depends on what sort of system we're talking about) then you have to decide how far ahead your
calendar is going to go, and precisely how you are going to represent holidays, weekends and so on. If you are only ever going to
have a "two business days ahead" requirement, you might as well have a database with two columns containing the precise result you
currentdate,dateplus2
2007-02-24,2007-02-27
Then you would need
You could even use a Word document to contain such a database if it weren't for the fact that Word doesn't consider the result to
be a date, so doesn't apply the date format, so may insert that extra paragraph I mentioned.
Peter Jamieson

Jezebel

2007-02-26 08:23:41 UTC

Permalink

That explains pretty clearly why you don't want to use this method for any
serious purpose.

Post by macropod
Hi Peter/Graham,
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
{SET jd{=jd+(MOD(jd,7)>4)*(4-MOD(jd,7))}}
or
{SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
after the existing 'SET' jd field would adjust the calculated dates to the
previous Friday or next Monday, respectively.
Dealing with public holidays could be problematic if they fall on Mondays
or Fridays, but only because they'd have to be coded for on a case-by-case
basis. VBA probably wouldn't be any easier in this regard.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and holidays
thing) you can try using a DATABASE field, but you need a Jet database to
do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,datevalue('{ DATE
or even
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert using ctrl-F9.
For earlier versions of Word you need a bit more to prevent Word trying
to open Access (which you may not have).
You may also encounter a number of security-related barriers to this approach.
The .mdb needs to be a valid Jet database, but it can be empty, i.e. have
a. If you have Access, it should be easy to create an empty .mdb.
b. Depending on what software is on your system you may be able to
right-click in Windows Explorer and use the "New" option to create a new
"Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to start the
Mail Merge Wizard, follow the task panes, and in "Select Recipients",
select "Type a new list", then click Create. Add a record, then save the
resulting .mdb
Whether it's advisable to use this approach is a different question. The
main drawback of is its reliance on an external file (and related
database access software) and the increased potential for maintenance
problems.
To deal with weekends you could use a more complicated expression, e.g. something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
dateadd('d',choose(weekday('{ d }'),2,2,2,2,4,4,3),'{ d }')"
NB in recent versions of Word, the result of a DATABASE field sometimes
includes a paragraph mark, which has rendered it useless for including
individual text values. If the result is a date or a number, a
date/numeric format switch seems to deal with that problem.
As for the holidays, even assuming you are only interested in one
country's holidays, you would only be able to build additional query
criteria in if the list of holidays was atypically small and predictable
(partly because the query text length is constrained, probably to 255
characters). Typically, you will simply have to maintain a calendar of
some kind. If you also keep that in a database (somewhere, depends on
what sort of system we're talking about) then you have to decide how far
ahead your calendar is going to go, and precisely how you are going to
represent holidays, weekends and so on. If you are only ever going to
have a "two business days ahead" requirement, you might as well have a
currentdate,dateplus2
2007-02-24,2007-02-27
Then you would need
{ DATABASE \d "your database path name" \s "SELECT dateplus2 FROM
You could even use a Word document to contain such a database if it
weren't for the fact that Word doesn't consider the result to be a date,
so doesn't apply the date format, so may insert that extra paragraph I
mentioned.
Peter Jamieson

macropod

2007-02-26 10:53:45 UTC

Permalink

OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this method for any
serious purpose.

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and holidays
thing) you can try using a DATABASE field, but you need a Jet database to
do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,datevalue('{ DATE
or even
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert using ctrl-F9.
For earlier versions of Word you need a bit more to prevent Word trying
to open Access (which you may not have).
You may also encounter a number of security-related barriers to this approach.
The .mdb needs to be a valid Jet database, but it can be empty, i.e. have
a. If you have Access, it should be easy to create an empty .mdb.
b. Depending on what software is on your system you may be able to
right-click in Windows Explorer and use the "New" option to create a new
"Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to start the
Mail Merge Wizard, follow the task panes, and in "Select Recipients",
select "Type a new list", then click Create. Add a record, then save the
resulting .mdb
Whether it's advisable to use this approach is a different question. The
main drawback of is its reliance on an external file (and related
database access software) and the increased potential for maintenance
problems.
To deal with weekends you could use a more complicated expression, e.g. something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
dateadd('d',choose(weekday('{ d }'),2,2,2,2,4,4,3),'{ d }')"
NB in recent versions of Word, the result of a DATABASE field sometimes
includes a paragraph mark, which has rendered it useless for including
individual text values. If the result is a date or a number, a
date/numeric format switch seems to deal with that problem.
As for the holidays, even assuming you are only interested in one
country's holidays, you would only be able to build additional query
criteria in if the list of holidays was atypically small and predictable
(partly because the query text length is constrained, probably to 255
characters). Typically, you will simply have to maintain a calendar of
some kind. If you also keep that in a database (somewhere, depends on
what sort of system we're talking about) then you have to decide how far
ahead your calendar is going to go, and precisely how you are going to
represent holidays, weekends and so on. If you are only ever going to
have a "two business days ahead" requirement, you might as well have a
currentdate,dateplus2
2007-02-24,2007-02-27
Then you would need
{ DATABASE \d "your database path name" \s "SELECT dateplus2 FROM
You could even use a Word document to contain such a database if it
weren't for the fact that Word doesn't consider the result to be a date,
so doesn't apply the date format, so may insert that extra paragraph I
mentioned.
Peter Jamieson

lwildernorva

2007-02-28 20:57:12 UTC

Permalink

This method should work for adding two days but skipping the weekend days. A
bookmark and a macro should do the trick. Create your letter template. My
simple template says "We would like the courtesy of a reply by []" where the
brackets are replaced by an inserted bookmark. For the purposes of this
question, I called the bookmark, "bkSecondDate" which represents the two days
in the future. The following code should automatically insert the advanced
date when run:

Sub AddDate()

Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range

strFirstDate = InputBox("Enter Beginning Date in Full Date Format, i.e.,
'November 1, 2007'")

IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate

End Sub

This code can be embedded in the template as a macro and fired to run when a
new document based on the template is created by using the AutoNew command.

Apparently without reference to an external database, to avoid holidays, you
would need to create additional coding that compares the date returned with a
set of dates to avoid, in this case annual holidays. This coding would have
to be revised annually in order to include those holidays that change from
year to year, such as Easter and Thanksgiving. I haven't yet generated this
code, but if I have time to come up with something, I'll post back.

Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this method for any
serious purpose.

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and holidays
thing) you can try using a DATABASE field, but you need a Jet database to
do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,datevalue('{ DATE
or even
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert using ctrl-F9.
For earlier versions of Word you need a bit more to prevent Word trying
to open Access (which you may not have).
You may also encounter a number of security-related barriers to this approach.
The .mdb needs to be a valid Jet database, but it can be empty, i.e. have
a. If you have Access, it should be easy to create an empty .mdb.
b. Depending on what software is on your system you may be able to
right-click in Windows Explorer and use the "New" option to create a new
"Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to start the
Mail Merge Wizard, follow the task panes, and in "Select Recipients",
select "Type a new list", then click Create. Add a record, then save the
resulting .mdb
Whether it's advisable to use this approach is a different question. The
main drawback of is its reliance on an external file (and related
database access software) and the increased potential for maintenance
problems.
To deal with weekends you could use a more complicated expression, e.g.
something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
NB in recent versions of Word, the result of a DATABASE field sometimes
includes a paragraph mark, which has rendered it useless for including
individual text values. If the result is a date or a number, a
date/numeric format switch seems to deal with that problem.
As for the holidays, even assuming you are only interested in one
country's holidays, you would only be able to build additional query
criteria in if the list of holidays was atypically small and predictable
(partly because the query text length is constrained, probably to 255
characters). Typically, you will simply have to maintain a calendar of
some kind. If you also keep that in a database (somewhere, depends on
what sort of system we're talking about) then you have to decide how far
ahead your calendar is going to go, and precisely how you are going to
represent holidays, weekends and so on. If you are only ever going to
have a "two business days ahead" requirement, you might as well have a
currentdate,dateplus2
2007-02-24,2007-02-27
Then you would need
{ DATABASE \d "your database path name" \s "SELECT dateplus2 FROM
You could even use a Word document to contain such a database if it
weren't for the fact that Word doesn't consider the result to be a date,
so doesn't apply the date format, so may insert that extra paragraph I
mentioned.
Peter Jamieson

lwildernorva

2007-02-28 22:03:30 UTC

Permalink

First quick stab at revising what I did earlier. I don't think it's very
efficient, but I'm looking for a better solution. The additional code takes
into account the following holidays, in order: Memorial Day, July 4th, Labor
Day, Columbus Day, Veterans Day, Thanksgiving Day and the Friday after
Thanksgiving Day, Christmas Eve, Christmas Day, New Year's Eve, and New
Year's Day '08:

Sub AddDate()

Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
Dim AvoidDate As Date

strFirstDate = InputBox("Enter Beginning Date in Full Date Format, i.e.,
'November 1, 2007'")

IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
Do While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #5/28/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #7/4/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #9/3/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #10/15/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/12/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/22/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/23/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/24/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/25/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/31/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #1/1/2008#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate

End Sub

As mentioned in my first message, the disadvantage to this approach is that
you must revise each holiday annually, both to adjust the holidays that
change dates from year to year and to adjust the year for each holiday. On
the other hand, if you don't have access to an external database of holidays
and store this macro in the template for your document so that it is easily
accessible, this method won't require that much work each year.

I'll try to revise this code to make it more efficient, but I had been
thinking about this problem in connection with another document I've been
working on so it gave me the motivation to tackle it this afternoon.

Post by lwildernorva
This method should work for adding two days but skipping the weekend days. A
bookmark and a macro should do the trick. Create your letter template. My
simple template says "We would like the courtesy of a reply by []" where the
brackets are replaced by an inserted bookmark. For the purposes of this
question, I called the bookmark, "bkSecondDate" which represents the two days
in the future. The following code should automatically insert the advanced
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format, i.e.,
'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
This code can be embedded in the template as a macro and fired to run when a
new document based on the template is created by using the AutoNew command.
Apparently without reference to an external database, to avoid holidays, you
would need to create additional coding that compares the date returned with a
set of dates to avoid, in this case annual holidays. This coding would have
to be revised annually in order to include those holidays that change from
year to year, such as Easter and Thanksgiving. I haven't yet generated this
code, but if I have time to come up with something, I'll post back.
Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this method for any
serious purpose.

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and holidays
thing) you can try using a DATABASE field, but you need a Jet database to
do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,datevalue('{ DATE
or even
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert using ctrl-F9.
For earlier versions of Word you need a bit more to prevent Word trying
to open Access (which you may not have).
You may also encounter a number of security-related barriers to this approach.
The .mdb needs to be a valid Jet database, but it can be empty, i.e. have
a. If you have Access, it should be easy to create an empty .mdb.
b. Depending on what software is on your system you may be able to
right-click in Windows Explorer and use the "New" option to create a new
"Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to start the
Mail Merge Wizard, follow the task panes, and in "Select Recipients",
select "Type a new list", then click Create. Add a record, then save the
resulting .mdb
Whether it's advisable to use this approach is a different question. The
main drawback of is its reliance on an external file (and related
database access software) and the increased potential for maintenance
problems.
To deal with weekends you could use a more complicated expression, e.g.
something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
NB in recent versions of Word, the result of a DATABASE field sometimes
includes a paragraph mark, which has rendered it useless for including
individual text values. If the result is a date or a number, a
date/numeric format switch seems to deal with that problem.
As for the holidays, even assuming you are only interested in one
country's holidays, you would only be able to build additional query
criteria in if the list of holidays was atypically small and predictable
(partly because the query text length is constrained, probably to 255
characters). Typically, you will simply have to maintain a calendar of
some kind. If you also keep that in a database (somewhere, depends on
what sort of system we're talking about) then you have to decide how far
ahead your calendar is going to go, and precisely how you are going to
represent holidays, weekends and so on. If you are only ever going to
have a "two business days ahead" requirement, you might as well have a
currentdate,dateplus2
2007-02-24,2007-02-27
Then you would need
{ DATABASE \d "your database path name" \s "SELECT dateplus2 FROM
You could even use a Word document to contain such a database if it
weren't for the fact that Word doesn't consider the result to be a date,
so doesn't apply the date format, so may insert that extra paragraph I
mentioned.
Peter Jamieson

Greg Maxey

2007-02-28 22:48:16 UTC

Permalink

While your method is working for this year, it would eventually hit a snag.
Consider if this case. It is today. For whatever reason March 2nd is now a
federal holiday. Running your code the the due date would be pushed to
March 3rd.

Not saying that the method below is good, it does prevent (or from my
limited testing it prevents) a due date from falling a weekend or specific
date.

Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
strFirstDate = InputBox("Enter Beginning Date in Full Date Format, i.e.,
'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
Do While DatePart("w", strSecondDate, vbMonday) > 5 Or
Holiday(strSecondDate) = True
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Selection.InsertAfter CStr(strSecondDate)
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #3/2/2007#, #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#, # _
11/12/2007#, #11/22/2007#, #11/23/2007#, #12/24/2007#,
#12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
First quick stab at revising what I did earlier. I don't think it's
very efficient, but I'm looking for a better solution. The
Memorial Day, July 4th, Labor Day, Columbus Day, Veterans Day,
Thanksgiving Day and the Friday after Thanksgiving Day, Christmas
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
Dim AvoidDate As Date
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
Do While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #5/28/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #7/4/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #9/3/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #10/15/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/12/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/22/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/23/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/24/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/25/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/31/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #1/1/2008#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
As mentioned in my first message, the disadvantage to this approach
is that you must revise each holiday annually, both to adjust the
holidays that change dates from year to year and to adjust the year
for each holiday. On the other hand, if you don't have access to an
external database of holidays and store this macro in the template
for your document so that it is easily accessible, this method won't
require that much work each year.
I'll try to revise this code to make it more efficient, but I had been
thinking about this problem in connection with another document I've
been working on so it gave me the motivation to tackle it this
afternoon.

Post by lwildernorva
This method should work for adding two days but skipping the weekend
days. A bookmark and a macro should do the trick. Create your
letter template. My simple template says "We would like the
courtesy of a reply by []" where the brackets are replaced by an
inserted bookmark. For the purposes of this question, I called the
bookmark, "bkSecondDate" which represents the two days in the
future. The following code should automatically insert the advanced
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
This code can be embedded in the template as a macro and fired to
run when a new document based on the template is created by using
the AutoNew command.
Apparently without reference to an external database, to avoid
holidays, you would need to create additional coding that compares
the date returned with a set of dates to avoid, in this case annual
holidays. This coding would have to be revised annually in order to
include those holidays that change from year to year, such as Easter
and Thanksgiving. I haven't yet generated this code, but if I have
time to come up with something, I'll post back.
Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this method
for any serious purpose.

Post by macropod
Hi Peter/Graham,
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
to cope with weekends would be fairly trivial. For example,
adding: {SET jd{=jd+(MOD(jd,7)>4)*(4-MOD(jd,7))}}
or
{SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
after the existing 'SET' jd field would adjust the calculated
dates to the previous Friday or next Monday, respectively.
Dealing with public holidays could be problematic if they fall on
Mondays or Fridays, but only because they'd have to be coded for
on a case-by-case basis. VBA probably wouldn't be any easier in this regard.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and
holidays thing) you can try using a DATABASE field, but you need
a Jet database to do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,datevalue('{
or even
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert using ctrl-F9.
For earlier versions of Word you need a bit more to prevent Word
trying to open Access (which you may not have).
You may also encounter a number of security-related barriers to this approach.
The .mdb needs to be a valid Jet database, but it can be empty,
i.e. have no visible queries or tables. There are various ways
a. If you have Access, it should be easy to create an empty .mdb.
b. Depending on what software is on your system you may be able
to right-click in Windows Explorer and use the "New" option to
create a new "Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to
start the Mail Merge Wizard, follow the task panes, and in
"Select Recipients", select "Type a new list", then click
Create. Add a record, then save the resulting .mdb
Whether it's advisable to use this approach is a different
question. The main drawback of is its reliance on an external
file (and related database access software) and the increased
potential for maintenance problems.
To deal with weekends you could use a more complicated
expression, e.g. something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
"SELECT dateadd('d',choose(weekday('{ d }'),2,2,2,2,4,4,3),'{ d
NB in recent versions of Word, the result of a DATABASE field
sometimes includes a paragraph mark, which has rendered it
useless for including individual text values. If the result is a
date or a number, a date/numeric format switch seems to deal
with that problem.
As for the holidays, even assuming you are only interested in one
country's holidays, you would only be able to build additional
query criteria in if the list of holidays was atypically small
and predictable (partly because the query text length is
constrained, probably to 255 characters). Typically, you will
simply have to maintain a calendar of some kind. If you also
keep that in a database (somewhere, depends on what sort of
system we're talking about) then you have to decide how far
ahead your calendar is going to go, and precisely how you are
going to represent holidays, weekends and so on. If you are only
ever going to have a "two business days ahead" requirement, you
might as well have a database with two columns containing the
currentdate,dateplus2
2007-02-24,2007-02-27
Then you would need
{ DATABASE \d "your database path name" \s "SELECT dateplus2 FROM
You could even use a Word document to contain such a database if
it weren't for the fact that Word doesn't consider the result to
be a date, so doesn't apply the date format, so may insert that
extra paragraph I mentioned.
Peter Jamieson

Greg Maxey

2007-02-28 23:14:05 UTC

Permalink

Refined somewhat:

Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
Err_ReEntry:
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or Holiday(pDueDate) = True
pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
Err_Handler:
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub

Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#, #11/12/2007#, #
_
11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function

Here is the problem that I see. If your user enters December 30, 2007 the
code will return 1/01/2008. We all know that is a holiday. You could add
something like:

.....
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found a flaw
with this macro. A date generated in 08 may fall on a holiday."
....

Which illustrates IMHO the complexity of this approach and certainly
illustrates that I am not up to solving it. Good luck.
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
This method should work for adding two days but skipping the weekend
days. A bookmark and a macro should do the trick. Create your
letter template. My simple template says "We would like the
courtesy of a reply by []" where the brackets are replaced by an
inserted bookmark. For the purposes of this question, I called the
bookmark, "bkSecondDate" which represents the two days in the
future. The following code should automatically insert the advanced
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
This code can be embedded in the template as a macro and fired to
run when a new document based on the template is created by using
the AutoNew command.
Apparently without reference to an external database, to avoid
holidays, you would need to create additional coding that compares
the date returned with a set of dates to avoid, in this case annual
holidays. This coding would have to be revised annually in order to
include those holidays that change from year to year, such as Easter
and Thanksgiving. I haven't yet generated this code, but if I have
time to come up with something, I'll post back.
Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this method
for any serious purpose.

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and
holidays thing) you can try using a DATABASE field, but you need
a Jet database to do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,datevalue('{
or even
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert using ctrl-F9.
For earlier versions of Word you need a bit more to prevent Word
trying to open Access (which you may not have).
You may also encounter a number of security-related barriers to this approach.
The .mdb needs to be a valid Jet database, but it can be empty,
i.e. have no visible queries or tables. There are various ways
a. If you have Access, it should be easy to create an empty .mdb.
b. Depending on what software is on your system you may be able
to right-click in Windows Explorer and use the "New" option to
create a new "Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to
start the Mail Merge Wizard, follow the task panes, and in
"Select Recipients", select "Type a new list", then click
Create. Add a record, then save the resulting .mdb
Whether it's advisable to use this approach is a different
question. The main drawback of is its reliance on an external
file (and related database access software) and the increased
potential for maintenance problems.
To deal with weekends you could use a more complicated
expression, e.g. something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
"SELECT dateadd('d',choose(weekday('{ d }'),2,2,2,2,4,4,3),'{ d
NB in recent versions of Word, the result of a DATABASE field
sometimes includes a paragraph mark, which has rendered it
useless for including individual text values. If the result is a
date or a number, a date/numeric format switch seems to deal
with that problem.
As for the holidays, even assuming you are only interested in one
country's holidays, you would only be able to build additional
query criteria in if the list of holidays was atypically small
and predictable (partly because the query text length is
constrained, probably to 255 characters). Typically, you will
simply have to maintain a calendar of some kind. If you also
keep that in a database (somewhere, depends on what sort of
system we're talking about) then you have to decide how far
ahead your calendar is going to go, and precisely how you are
going to represent holidays, weekends and so on. If you are only
ever going to have a "two business days ahead" requirement, you
might as well have a database with two columns containing the
currentdate,dateplus2
2007-02-24,2007-02-27
Then you would need
{ DATABASE \d "your database path name" \s "SELECT dateplus2 FROM
You could even use a Word document to contain such a database if
it weren't for the fact that Word doesn't consider the result to
be a date, so doesn't apply the date format, so may insert that
extra paragraph I mentioned.
Peter Jamieson

lwildernorva

2007-03-01 01:08:27 UTC

Permalink

You found the problem before I could solve it. While away again working on
the problem, I discovered that the placement of one test at the beginning of
the macro to determine whether the generated date landed on a weekend
produced a problem later. You found it around the New Year's holiday while I
found it at Thanksgiving. The code as previously written returned dates on
the weekend that followed the Friday after Thanksgiving.

One of the solutions I tried was using multiple Do While statements that
again searched for weekend dates. The fallacy of this approach is that the
code, cumbersome already, became even more so since I was repeating
conditions the macro had already searched for. I was looking at a Select
Case solution when I decided to give up for the night. Then I checked back
to see if any additional messages had been posted to this topic and saw your
replies. You posted a much more effective and elegant solution!

The macro still requires annual revisions, but given that each
organization's holidays may vary (when I changed jobs I went from six to
thirteen holidays per year), I'm not so sure that is a real problem.

Post by lwildernorva
Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or Holiday(pDueDate) = True
pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#, #11/12/2007#, #
_
11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
Here is the problem that I see. If your user enters December 30, 2007 the
code will return 1/01/2008. We all know that is a holiday. You could add
......
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found a flaw
with this macro. A date generated in 08 may fall on a holiday."
.....
Which illustrates IMHO the complexity of this approach and certainly
illustrates that I am not up to solving it. Good luck.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
This method should work for adding two days but skipping the weekend
days. A bookmark and a macro should do the trick. Create your
letter template. My simple template says "We would like the
courtesy of a reply by []" where the brackets are replaced by an
inserted bookmark. For the purposes of this question, I called the
bookmark, "bkSecondDate" which represents the two days in the
future. The following code should automatically insert the advanced
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
This code can be embedded in the template as a macro and fired to
run when a new document based on the template is created by using
the AutoNew command.
Apparently without reference to an external database, to avoid
holidays, you would need to create additional coding that compares
the date returned with a set of dates to avoid, in this case annual
holidays. This coding would have to be revised annually in order to
include those holidays that change from year to year, such as Easter
and Thanksgiving. I haven't yet generated this code, but if I have
time to come up with something, I'll post back.
Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this method
for any serious purpose.

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and
holidays thing) you can try using a DATABASE field, but you need
a Jet database to do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,datevalue('{
or even
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert using ctrl-F9.
For earlier versions of Word you need a bit more to prevent Word
trying to open Access (which you may not have).
You may also encounter a number of security-related barriers to
this approach.
The .mdb needs to be a valid Jet database, but it can be empty,
i.e. have no visible queries or tables. There are various ways
a. If you have Access, it should be easy to create an empty .mdb.
b. Depending on what software is on your system you may be able
to right-click in Windows Explorer and use the "New" option to
create a new "Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it
to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to
start the Mail Merge Wizard, follow the task panes, and in
"Select Recipients", select "Type a new list", then click
Create. Add a record, then save the resulting .mdb
Whether it's advisable to use this approach is a different
question. The main drawback of is its reliance on an external
file (and related database access software) and the increased
potential for maintenance problems.
To deal with weekends you could use a more complicated
expression, e.g. something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
"SELECT dateadd('d',choose(weekday('{ d }'),2,2,2,2,4,4,3),'{ d
NB in recent versions of Word, the result of a DATABASE field
sometimes includes a paragraph mark, which has rendered it
useless for including individual text values. If the result is a
date or a number, a date/numeric format switch seems to deal
with that problem.
As for the holidays, even assuming you are only interested in one
country's holidays, you would only be able to build additional
query criteria in if the list of holidays was atypically small
and predictable (partly because the query text length is
constrained, probably to 255 characters). Typically, you will
simply have to maintain a calendar of some kind. If you also
keep that in a database (somewhere, depends on what sort of
system we're talking about) then you have to decide how far
ahead your calendar is going to go, and precisely how you are
going to represent holidays, weekends and so on. If you are only
ever going to have a "two business days ahead" requirement, you
might as well have a database with two columns containing the
currentdate,dateplus2

Greg Maxey

2007-03-01 01:25:51 UTC

Permalink

Elegant? I don't know about that.

You can minimize the annual adjustments somewhat using an approach something
this in the Function:

Function Holiday(pDate As Date) As Boolean
Dim oStr As String
'Fixed holidays
oStr = Format(pDate, "mm/dd")
Select Case oStr
Case "12/24", "12/25", "12/31", "7/4", "1/1"
Holiday = True
Exit Function
Case Else
Holiday = False
End Select
'Variable holidays
Select Case pDate
Case #5/28/2007#, #9/3/2007#, #10/15/2007#, #11/12/2007#, #11/22/2007#,
#11/23/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
You found the problem before I could solve it. While away again
working on the problem, I discovered that the placement of one test
at the beginning of the macro to determine whether the generated date
landed on a weekend produced a problem later. You found it around
the New Year's holiday while I found it at Thanksgiving. The code as
previously written returned dates on the weekend that followed the
Friday after Thanksgiving.
One of the solutions I tried was using multiple Do While statements
that again searched for weekend dates. The fallacy of this approach
is that the code, cumbersome already, became even more so since I was
repeating conditions the macro had already searched for. I was
looking at a Select Case solution when I decided to give up for the
night. Then I checked back to see if any additional messages had
been posted to this topic and saw your replies. You posted a much
more effective and elegant solution!
The macro still requires annual revisions, but given that each
organization's holidays may vary (when I changed jobs I went from six
to thirteen holidays per year), I'm not so sure that is a real
problem.

Post by lwildernorva
Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or Holiday(pDueDate)
= True pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#,
#11/12/2007#, # _
11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
Here is the problem that I see. If your user enters December 30,
2007 the code will return 1/01/2008. We all know that is a holiday.
......
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found
a flaw with this macro. A date generated in 08 may fall on a
holiday." .....
Which illustrates IMHO the complexity of this approach and certainly
illustrates that I am not up to solving it. Good luck.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
First quick stab at revising what I did earlier. I don't think it's
very efficient, but I'm looking for a better solution. The
Memorial Day, July 4th, Labor Day, Columbus Day, Veterans Day,
Thanksgiving Day and the Friday after Thanksgiving Day, Christmas
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
Dim AvoidDate As Date
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
Do While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #5/28/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #7/4/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #9/3/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #10/15/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/12/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/22/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/23/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/24/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/25/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/31/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #1/1/2008#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
As mentioned in my first message, the disadvantage to this approach
is that you must revise each holiday annually, both to adjust the
holidays that change dates from year to year and to adjust the year
for each holiday. On the other hand, if you don't have access to an
external database of holidays and store this macro in the template
for your document so that it is easily accessible, this method won't
require that much work each year.
I'll try to revise this code to make it more efficient, but I had
been thinking about this problem in connection with another
document I've been working on so it gave me the motivation to
tackle it this afternoon.

Post by lwildernorva
This method should work for adding two days but skipping the
weekend days. A bookmark and a macro should do the trick. Create
your letter template. My simple template says "We would like the
courtesy of a reply by []" where the brackets are replaced by an
inserted bookmark. For the purposes of this question, I called the
bookmark, "bkSecondDate" which represents the two days in the
future. The following code should automatically insert the
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
This code can be embedded in the template as a macro and fired to
run when a new document based on the template is created by using
the AutoNew command.
Apparently without reference to an external database, to avoid
holidays, you would need to create additional coding that compares
the date returned with a set of dates to avoid, in this case annual
holidays. This coding would have to be revised annually in order
to include those holidays that change from year to year, such as
Easter and Thanksgiving. I haven't yet generated this code, but
if I have time to come up with something, I'll post back.
Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this
method for any serious purpose.

Post by macropod
Hi Peter/Graham,
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
to cope with weekends would be fairly trivial. For example,
adding: {SET jd{=jd+(MOD(jd,7)>4)*(4-MOD(jd,7))}}
or
{SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
after the existing 'SET' jd field would adjust the calculated
dates to the previous Friday or next Monday, respectively.
Dealing with public holidays could be problematic if they fall
on Mondays or Fridays, but only because they'd have to be coded
for on a case-by-case basis. VBA probably wouldn't be any
easier in this regard.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and
holidays thing) you can try using a DATABASE field, but you
need a Jet database to do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
or even
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert using ctrl-F9.
For earlier versions of Word you need a bit more to prevent
Word trying to open Access (which you may not have).
You may also encounter a number of security-related barriers to
this approach.
The .mdb needs to be a valid Jet database, but it can be empty,
i.e. have no visible queries or tables. There are various ways
a. If you have Access, it should be easy to create an empty
.mdb. b. Depending on what software is on your system you may
be able to right-click in Windows Explorer and use the "New"
option to create a new "Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it
to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to
start the Mail Merge Wizard, follow the task panes, and in
"Select Recipients", select "Type a new list", then click
Create. Add a record, then save the resulting .mdb
Whether it's advisable to use this approach is a different
question. The main drawback of is its reliance on an external
file (and related database access software) and the increased
potential for maintenance problems.
To deal with weekends you could use a more complicated
expression, e.g. something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
\s "SELECT dateadd('d',choose(weekday('{ d
NB in recent versions of Word, the result of a DATABASE field
sometimes includes a paragraph mark, which has rendered it
useless for including individual text values. If the result is
a date or a number, a date/numeric format switch seems to deal
with that problem.
As for the holidays, even assuming you are only interested in
one country's holidays, you would only be able to build
additional query criteria in if the list of holidays was
atypically small and predictable (partly because the query
text length is constrained, probably to 255 characters).
Typically, you will simply have to maintain a calendar of some
kind. If you also keep that in a database (somewhere, depends
on what sort of system we're talking about) then you have to
decide how far ahead your calendar is going to go, and
precisely how you are going to represent holidays, weekends
and so on. If you are only ever going to have a "two business
days ahead" requirement, you might as well have a database
currentdate,dateplus2

lwildernorva

2007-03-01 19:14:03 UTC

Permalink

Just let me clear up some confusion about your second post back; so long as
the user added 12/21/07 & 1/1/08 to the code, the problem you cite about
entering December 30, 2007 is resolved. Given the original poster's interest
in creating a macro that would post a date only two days later than the
document itself, I felt that most of the problems with encountering dates
beyond the macro's reach could be solved by yearly maintenance.

For instance, I use a calculation program to determine a compensation rate
that changes yearly, with certain minimums and maximums mandated, so I must
revise my calculation program on an annual basis. The new compensation rate
is published only about six months before it takes effect, so I revise the
program as soon as the rates are published.

I was interested in this topic because I have been working on a similar
document that would require calculation of a number of dates backwards from
the initial date entered. As with the original poster, I wanted to avoid
weekends and holidays. I figured out the weekends pretty easily, but the
work developing code to avoid holidays really highlighted the danger in
continuing to use the logic that solved the simpler problem of avoiding
weekends--it made the code much more convoluted. That's why I had started
using the Select Case method (already used in my calculation program), but I
wasn't as comfortable using True-False statements as I would like. Your
solution does seem "elegant" when compared with the approach I was taking.

BTW, some of the dates I'll enter and calculate back from go well beyond the
two days that were the concern of the original poster, and thinking about
this issue in this context has made me realize that I probably need to revise
the code quarterly rather than annually to insure I don't hit snags. Given
the amount of time my secretary and I will save using this macro, however, I
guarantee you the two minutes researching the dates of the next quarter's
holidays and the two minutes revising the code are well worth it!

Post by Greg Maxey
Elegant? I don't know about that.
You can minimize the annual adjustments somewhat using an approach something
Function Holiday(pDate As Date) As Boolean
Dim oStr As String
'Fixed holidays
oStr = Format(pDate, "mm/dd")
Select Case oStr
Case "12/24", "12/25", "12/31", "7/4", "1/1"
Holiday = True
Exit Function
Case Else
Holiday = False
End Select
'Variable holidays
Select Case pDate
Case #5/28/2007#, #9/3/2007#, #10/15/2007#, #11/12/2007#, #11/22/2007#,
#11/23/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or Holiday(pDueDate)
= True pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#,
#11/12/2007#, # _
11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
Here is the problem that I see. If your user enters December 30,
2007 the code will return 1/01/2008. We all know that is a holiday.
......
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found
a flaw with this macro. A date generated in 08 may fall on a
holiday." .....
Which illustrates IMHO the complexity of this approach and certainly
illustrates that I am not up to solving it. Good luck.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
First quick stab at revising what I did earlier. I don't think it's
very efficient, but I'm looking for a better solution. The
Memorial Day, July 4th, Labor Day, Columbus Day, Veterans Day,
Thanksgiving Day and the Friday after Thanksgiving Day, Christmas
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
Dim AvoidDate As Date
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
Do While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #5/28/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #7/4/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #9/3/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #10/15/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/12/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/22/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/23/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/24/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/25/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/31/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #1/1/2008#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
As mentioned in my first message, the disadvantage to this approach
is that you must revise each holiday annually, both to adjust the
holidays that change dates from year to year and to adjust the year
for each holiday. On the other hand, if you don't have access to an
external database of holidays and store this macro in the template
for your document so that it is easily accessible, this method won't
require that much work each year.
I'll try to revise this code to make it more efficient, but I had
been thinking about this problem in connection with another
document I've been working on so it gave me the motivation to
tackle it this afternoon.

Post by lwildernorva
This method should work for adding two days but skipping the
weekend days. A bookmark and a macro should do the trick. Create
your letter template. My simple template says "We would like the
courtesy of a reply by []" where the brackets are replaced by an
inserted bookmark. For the purposes of this question, I called the
bookmark, "bkSecondDate" which represents the two days in the
future. The following code should automatically insert the
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
This code can be embedded in the template as a macro and fired to
run when a new document based on the template is created by using
the AutoNew command.
Apparently without reference to an external database, to avoid
holidays, you would need to create additional coding that compares
the date returned with a set of dates to avoid, in this case annual
holidays. This coding would have to be revised annually in order
to include those holidays that change from year to year, such as
Easter and Thanksgiving. I haven't yet generated this code, but
if I have time to come up with something, I'll post back.
Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this
method for any serious purpose.

Post by macropod
Hi Peter/Graham,
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
to cope with weekends would be fairly trivial. For example,
adding: {SET jd{=jd+(MOD(jd,7)>4)*(4-MOD(jd,7))}}
or
{SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
after the existing 'SET' jd field would adjust the calculated
dates to the previous Friday or next Monday, respectively.
Dealing with public holidays could be problematic if they fall
on Mondays or Fridays, but only because they'd have to be coded
for on a case-by-case basis. VBA probably wouldn't be any
easier in this regard.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Greg Maxey

2007-03-01 23:32:18 UTC

Permalink

All confusion cleared up. I never read in this post until I saw your method
as I usually avoid the volleys between Jezebel and macropod ;-).

I tried something from scratch over a year ago that failed miserably. If my
code is elegant it is only because I saw it in the rough gem that you
posted. I saw almost instantly that you had a good idea, but that you
would eventually push a holiday into a weekend.

My comment about it being too complex for me to tackle is related to a total
solution, i.e., a macro that would work today, next year, last year, next
century, etc.without periodic ajustments. I don't know how to sovle that.

For your purposes, I think you have a very workable solution.

I don't know if it is something you need, but if you are interested in
future, past dates, date sequences, date spans, etc. you might look at my
date sequencer Addin.

http://gregmaxey.mvps.org/Date_Sequencer.htm
--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
Just let me clear up some confusion about your second post back; so long as
the user added 12/21/07 & 1/1/08 to the code, the problem you cite about
entering December 30, 2007 is resolved. Given the original poster's interest
in creating a macro that would post a date only two days later than the
document itself, I felt that most of the problems with encountering dates
beyond the macro's reach could be solved by yearly maintenance.
For instance, I use a calculation program to determine a compensation rate
that changes yearly, with certain minimums and maximums mandated, so I must
revise my calculation program on an annual basis. The new compensation rate
is published only about six months before it takes effect, so I revise the
program as soon as the rates are published.
I was interested in this topic because I have been working on a similar
document that would require calculation of a number of dates backwards from
the initial date entered. As with the original poster, I wanted to avoid
weekends and holidays. I figured out the weekends pretty easily, but the
work developing code to avoid holidays really highlighted the danger in
continuing to use the logic that solved the simpler problem of avoiding
weekends--it made the code much more convoluted. That's why I had started
using the Select Case method (already used in my calculation program), but I
wasn't as comfortable using True-False statements as I would like. Your
solution does seem "elegant" when compared with the approach I was taking.
BTW, some of the dates I'll enter and calculate back from go well beyond the
two days that were the concern of the original poster, and thinking about
this issue in this context has made me realize that I probably need to revise
the code quarterly rather than annually to insure I don't hit snags.
Given
the amount of time my secretary and I will save using this macro, however, I
guarantee you the two minutes researching the dates of the next quarter's
holidays and the two minutes revising the code are well worth it!

Post by lwildernorva
Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or Holiday(pDueDate)
= True pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#,
#11/12/2007#, # _
11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
Here is the problem that I see. If your user enters December 30,
2007 the code will return 1/01/2008. We all know that is a holiday.
......
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found
a flaw with this macro. A date generated in 08 may fall on a
holiday." .....
Which illustrates IMHO the complexity of this approach and certainly
illustrates that I am not up to solving it. Good luck.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
First quick stab at revising what I did earlier. I don't think it's
very efficient, but I'm looking for a better solution. The
Memorial Day, July 4th, Labor Day, Columbus Day, Veterans Day,
Thanksgiving Day and the Friday after Thanksgiving Day, Christmas
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
Dim AvoidDate As Date
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
Do While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #5/28/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #7/4/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #9/3/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #10/15/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/12/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/22/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/23/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/24/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/25/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/31/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #1/1/2008#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
As mentioned in my first message, the disadvantage to this approach
is that you must revise each holiday annually, both to adjust the
holidays that change dates from year to year and to adjust the year
for each holiday. On the other hand, if you don't have access to an
external database of holidays and store this macro in the template
for your document so that it is easily accessible, this method won't
require that much work each year.
I'll try to revise this code to make it more efficient, but I had
been thinking about this problem in connection with another
document I've been working on so it gave me the motivation to
tackle it this afternoon.

Post by lwildernorva
This method should work for adding two days but skipping the
weekend days. A bookmark and a macro should do the trick. Create
your letter template. My simple template says "We would like the
courtesy of a reply by []" where the brackets are replaced by an
inserted bookmark. For the purposes of this question, I called the
bookmark, "bkSecondDate" which represents the two days in the
future. The following code should automatically insert the
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
This code can be embedded in the template as a macro and fired to
run when a new document based on the template is created by using
the AutoNew command.
Apparently without reference to an external database, to avoid
holidays, you would need to create additional coding that compares
the date returned with a set of dates to avoid, in this case annual
holidays. This coding would have to be revised annually in order
to include those holidays that change from year to year, such as
Easter and Thanksgiving. I haven't yet generated this code, but
if I have time to come up with something, I'll post back.
Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this
method for any serious purpose.

Post by macropod
Hi Peter/Graham,
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
to cope with weekends would be fairly trivial. For example,
adding: {SET jd{=jd+(MOD(jd,7)>4)*(4-MOD(jd,7))}}
or
{SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
after the existing 'SET' jd field would adjust the calculated
dates to the previous Friday or next Monday, respectively.
Dealing with public holidays could be problematic if they fall
on Mondays or Fridays, but only because they'd have to be coded
for on a case-by-case basis. VBA probably wouldn't be any
easier in this regard.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Peter Jamieson

2007-03-02 10:09:22 UTC

Permalink

Post by Greg Maxey
My comment about it being too complex for me to tackle is related to a
total solution, i.e., a macro that would work today, next year, last year,
next century, etc.without periodic ajustments. I don't know how to sovle
that.

You can't solve the general case where you want anto add/subtract an
arbitrary number of days to/from a date and cope with holidays, for a number
of reasons (e.g. no-one knows what the holidays are going to be for sure in
the future). You have to pin down the requirement - what period are you
talking about, what calculations do you want to do, what are the relevant
days (and in international trading systems, for example, even working that
out is a black art, or used to be) and so on. Then you've got to decide how
to implement that requirement. Even if you've identified a really simple
requirement there's a maintenance issue, i.e. you may have to modify the
holiday dates. That means you have to remember to do it, and you have to
remember what to look at. Even if you have one piece of code that does what
you need and you have a strict timetable for reviewing it, if you re-use
that code by copying it the maintenance problem will swiftly get out of
hand. Personally I favour a database-oriented approach where you
a. keep all the date-related info. you want, as far ahead as you choose, in
a database
b. you implement the algorithms you need in the database, e.g. as stored
procedures or functions
c. you provide the interfaces you need for the software that needs to do
these calculations.

Obviously, you still have to remember to review your dates, and you have to
maintain the data. But at least everything that changes is then in one
place, and if your code is properly designed and written, none of the
software that uses the date-related functions need change over time.

FWIW I had a look around for "web services" that provide this kind of
facility - I didn't find any obvious candidates, but that doesn't mean they
don't exist. There are certainly several web sites with extensive lists of
public holidays.

Peter Jamieson

The reason I would always opt for a database-based solution except in the
simplest cases is because
and when can you pin them down
when are the holidays and when do you get to know where, if for no other
reason than you don't know when the holdisays w. You have to pin down the
requirement.
You really have to use an approach based on an external database

Post by Greg Maxey
All confusion cleared up. I never read in this post until I saw your
method as I usually avoid the volleys between Jezebel and macropod ;-).
I tried something from scratch over a year ago that failed miserably. If
my code is elegant it is only because I saw it in the rough gem that you
posted. I saw almost instantly that you had a good idea, but that you
would eventually push a holiday into a weekend.
My comment about it being too complex for me to tackle is related to a
total solution, i.e., a macro that would work today, next year, last year,
next century, etc.without periodic ajustments. I don't know how to sovle
that.
For your purposes, I think you have a very workable solution.
I don't know if it is something you need, but if you are interested in
future, past dates, date sequences, date spans, etc. you might look at my
date sequencer Addin.
http://gregmaxey.mvps.org/Date_Sequencer.htm
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by Greg Maxey
Elegant? I don't know about that.
You can minimize the annual adjustments somewhat using an approach something
Function Holiday(pDate As Date) As Boolean
Dim oStr As String
'Fixed holidays
oStr = Format(pDate, "mm/dd")
Select Case oStr
Case "12/24", "12/25", "12/31", "7/4", "1/1"
Holiday = True
Exit Function
Case Else
Holiday = False
End Select
'Variable holidays
Select Case pDate
Case #5/28/2007#, #9/3/2007#, #10/15/2007#, #11/12/2007#,
#11/22/2007#,
#11/23/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or Holiday(pDueDate)
= True pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#,
#11/12/2007#, # _
11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
Here is the problem that I see. If your user enters December 30,
2007 the code will return 1/01/2008. We all know that is a holiday.
......
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found
a flaw with this macro. A date generated in 08 may fall on a
holiday." .....
Which illustrates IMHO the complexity of this approach and certainly
illustrates that I am not up to solving it. Good luck.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
First quick stab at revising what I did earlier. I don't think it's
very efficient, but I'm looking for a better solution. The
Memorial Day, July 4th, Labor Day, Columbus Day, Veterans Day,
Thanksgiving Day and the Friday after Thanksgiving Day, Christmas
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
Dim AvoidDate As Date
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
Do While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #5/28/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #7/4/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #9/3/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #10/15/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/12/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/22/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/23/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/24/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/25/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/31/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #1/1/2008#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
As mentioned in my first message, the disadvantage to this approach
is that you must revise each holiday annually, both to adjust the
holidays that change dates from year to year and to adjust the year
for each holiday. On the other hand, if you don't have access to an
external database of holidays and store this macro in the template
for your document so that it is easily accessible, this method won't
require that much work each year.
I'll try to revise this code to make it more efficient, but I had
been thinking about this problem in connection with another
document I've been working on so it gave me the motivation to
tackle it this afternoon.

Post by lwildernorva
This method should work for adding two days but skipping the
weekend days. A bookmark and a macro should do the trick. Create
your letter template. My simple template says "We would like the
courtesy of a reply by []" where the brackets are replaced by an
inserted bookmark. For the purposes of this question, I called the
bookmark, "bkSecondDate" which represents the two days in the
future. The following code should automatically insert the
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
This code can be embedded in the template as a macro and fired to
run when a new document based on the template is created by using
the AutoNew command.
Apparently without reference to an external database, to avoid
holidays, you would need to create additional coding that compares
the date returned with a set of dates to avoid, in this case annual
holidays. This coding would have to be revised annually in order
to include those holidays that change from year to year, such as
Easter and Thanksgiving. I haven't yet generated this code, but
if I have time to come up with something, I'll post back.
Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this
method for any serious purpose.

Post by macropod
Hi Peter/Graham,
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
to cope with weekends would be fairly trivial. For example,
adding: {SET jd{=jd+(MOD(jd,7)>4)*(4-MOD(jd,7))}}
or
{SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
after the existing 'SET' jd field would adjust the calculated
dates to the previous Friday or next Monday, respectively.
Dealing with public holidays could be problematic if they fall
on Mondays or Fridays, but only because they'd have to be coded
for on a case-by-case basis. VBA probably wouldn't be any
easier in this regard.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Greg Maxey

2007-03-02 13:26:28 UTC

Permalink

Peter,

Thanks for your comments. Mine were prehaps misleading in that I
don't really have a problem to solve or a real need for a process to
advance dates. I just got interested in the VBA code and did my
best. My best fails as a total solution and that solution may in fact
be a database as you describe.

Post by Peter Jamieson

You can't solve the general case where you want anto add/subtract an
arbitrary number of days to/from a date and cope with holidays, for a number
of reasons (e.g. no-one knows what the holidays are going to be for sure in
the future). You have to pin down the requirement - what period are you
talking about, what calculations do you want to do, what are the relevant
days (and in international trading systems, for example, even working that
out is a black art, or used to be) and so on. Then you've got to decide how
to implement that requirement. Even if you've identified a really simple
requirement there's a maintenance issue, i.e. you may have to modify the
holiday dates. That means you have to remember to do it, and you have to
remember what to look at. Even if you have one piece of code that does what
you need and you have a strict timetable for reviewing it, if you re-use
that code by copying it the maintenance problem will swiftly get out of
hand. Personally I favour a database-oriented approach where you
a. keep all the date-related info. you want, as far ahead as you choose, in
a database
b. you implement the algorithms you need in the database, e.g. as stored
procedures or functions
c. you provide the interfaces you need for the software that needs to do
these calculations.
Obviously, you still have to remember to review your dates, and you have to
maintain the data. But at least everything that changes is then in one
place, and if your code is properly designed and written, none of the
software that uses the date-related functions need change over time.
FWIW I had a look around for "web services" that provide this kind of
facility - I didn't find any obvious candidates, but that doesn't mean they
don't exist. There are certainly several web sites with extensive lists of
public holidays.
Peter Jamieson
The reason I would always opt for a database-based solution except in the
simplest cases is because
and when can you pin them down
when are the holidays and when do you get to know where, if for no other
reason than you don't know when the holdisays w. You have to pin down the
requirement.

Post by lwildernorva
Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or Holiday(pDueDate)
= True pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#,
#11/12/2007#, # _
11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
Here is the problem that I see. If your user enters December 30,
2007 the code will return 1/01/2008. We all know that is a holiday.
......
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found
a flaw with this macro. A date generated in 08 may fall on a
holiday." .....
Which illustrates IMHO the complexity of this approach and certainly
illustrates that I am not up to solving it. Good luck.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

...
read more »- Hide quoted text -
- Show quoted text -

Peter Jamieson

2007-03-02 19:17:39 UTC

Permalink

Greg,

<<
I just got interested in the VBA code and did my
best.
IMO it's all about tradeoffs. If you need something that works for the next
week/month/year, there's a lot to be said for a simple VBA-only solution
with limited objectives. As a guy who's been called out many times to fix
long-gone developers' work, I tend to take a different view of things. But
it is still only one view.

Peter Jamieson

"Greg Maxey" <***@gmail.com> wrote in message news:***@64g2000cwx.googlegroups.com...
Peter,

Thanks for your comments. Mine were prehaps misleading in that I
don't really have a problem to solve or a real need for a process to
advance dates. I just got interested in the VBA code and did my
best. My best fails as a total solution and that solution may in fact
be a database as you describe.

Post by Peter Jamieson

You can't solve the general case where you want anto add/subtract an
arbitrary number of days to/from a date and cope with holidays, for a number
of reasons (e.g. no-one knows what the holidays are going to be for sure in
the future). You have to pin down the requirement - what period are you
talking about, what calculations do you want to do, what are the relevant
days (and in international trading systems, for example, even working that
out is a black art, or used to be) and so on. Then you've got to decide how
to implement that requirement. Even if you've identified a really simple
requirement there's a maintenance issue, i.e. you may have to modify the
holiday dates. That means you have to remember to do it, and you have to
remember what to look at. Even if you have one piece of code that does what
you need and you have a strict timetable for reviewing it, if you re-use
that code by copying it the maintenance problem will swiftly get out of
hand. Personally I favour a database-oriented approach where you
a. keep all the date-related info. you want, as far ahead as you choose, in
a database
b. you implement the algorithms you need in the database, e.g. as stored
procedures or functions
c. you provide the interfaces you need for the software that needs to do
these calculations.
Obviously, you still have to remember to review your dates, and you have to
maintain the data. But at least everything that changes is then in one
place, and if your code is properly designed and written, none of the
software that uses the date-related functions need change over time.
FWIW I had a look around for "web services" that provide this kind of
facility - I didn't find any obvious candidates, but that doesn't mean they
don't exist. There are certainly several web sites with extensive lists of
public holidays.
Peter Jamieson
The reason I would always opt for a database-based solution except in the
simplest cases is because
and when can you pin them down
when are the holidays and when do you get to know where, if for no other
reason than you don't know when the holdisays w. You have to pin down the
requirement.
You really have to use an approach based on an external database"Greg

Post by Greg Maxey
All confusion cleared up. I never read in this post until I saw your
method as I usually avoid the volleys between Jezebel and macropod ;-).
I tried something from scratch over a year ago that failed miserably.
If
my code is elegant it is only because I saw it in the rough gem that you
posted. I saw almost instantly that you had a good idea, but that you
would eventually push a holiday into a weekend.
My comment about it being too complex for me to tackle is related to a
total solution, i.e., a macro that would work today, next year, last year,
next century, etc.without periodic ajustments. I don't know how to sovle
that.
For your purposes, I think you have a very workable solution.
I don't know if it is something you need, but if you are interested in
future, past dates, date sequences, date spans, etc. you might look at my
date sequencer Addin.
http://gregmaxey.mvps.org/Date_Sequencer.htm
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
Just let me clear up some confusion about your second post back; so
long
as
the user added 12/21/07 & 1/1/08 to the code, the problem you cite about
entering December 30, 2007 is resolved. Given the original poster's interest
in creating a macro that would post a date only two days later than the
document itself, I felt that most of the problems with encountering dates
beyond the macro's reach could be solved by yearly maintenance.
For instance, I use a calculation program to determine a compensation rate
that changes yearly, with certain minimums and maximums mandated, so I must
revise my calculation program on an annual basis. The new compensation rate
is published only about six months before it takes effect, so I revise the
program as soon as the rates are published.
I was interested in this topic because I have been working on a similar
document that would require calculation of a number of dates backwards from
the initial date entered. As with the original poster, I wanted to avoid
weekends and holidays. I figured out the weekends pretty easily, but the
work developing code to avoid holidays really highlighted the danger in
continuing to use the logic that solved the simpler problem of avoiding
weekends--it made the code much more convoluted. That's why I had started
using the Select Case method (already used in my calculation program), but I
wasn't as comfortable using True-False statements as I would like.
Your
solution does seem "elegant" when compared with the approach I was taking.
BTW, some of the dates I'll enter and calculate back from go well
beyond
the
two days that were the concern of the original poster, and thinking about
this issue in this context has made me realize that I probably need to revise
the code quarterly rather than annually to insure I don't hit snags.
Given
the amount of time my secretary and I will save using this macro, however, I
guarantee you the two minutes researching the dates of the next quarter's
holidays and the two minutes revising the code are well worth it!

Post by lwildernorva
Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or Holiday(pDueDate)
= True pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#,
#11/12/2007#, # _
11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
Here is the problem that I see. If your user enters December 30,
2007 the code will return 1/01/2008. We all know that is a holiday.
......
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found
a flaw with this macro. A date generated in 08 may fall on a
holiday." .....
Which illustrates IMHO the complexity of this approach and certainly
illustrates that I am not up to solving it. Good luck.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

...
read more »- Hide quoted text -
- Show quoted text -

Doug Robbins - Word MVP

2007-03-03 17:06:13 UTC

Permalink

For what it's worth, I started messing with this, using
System.PrivateProfileString to look for the holidays in the present year and
display an inputbox into which the user could enter them as follows:

Dim ThisYear As String, Message As String, Title As String
Dim Holidays As Variant
Dim d1 As Date, d2 As Date
Message = "Enter the dates of each holiday in the format MM/dd with each one
separated by '|' with no spaces."
Title = "Holidays"
ThisYear = Format(Date, "yyyy")
If System.PrivateProfileString("c:\Holidays.txt", _
"MacroSettings", ThisYear) = "" Then
System.PrivateProfileString("c:\Holidays.txt", _
"MacroSettings", ThisYear) = InputBox(Message, Title)
End If
Holidays = Split(System.PrivateProfileString("c:\Holidays.txt", _
"MacroSettings", ThisYear), "|")
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Post by Greg Maxey
Greg,
<<
I just got interested in the VBA code and did my
best.
IMO it's all about tradeoffs. If you need something that works for the
next week/month/year, there's a lot to be said for a simple VBA-only
solution with limited objectives. As a guy who's been called out many
times to fix long-gone developers' work, I tend to take a different view
of things. But it is still only one view.
Peter Jamieson
Peter,
Thanks for your comments. Mine were prehaps misleading in that I
don't really have a problem to solve or a real need for a process to
advance dates. I just got interested in the VBA code and did my
best. My best fails as a total solution and that solution may in fact
be a database as you describe.

Post by Peter Jamieson

You can't solve the general case where you want anto add/subtract an
arbitrary number of days to/from a date and cope with holidays, for a number
of reasons (e.g. no-one knows what the holidays are going to be for sure in
the future). You have to pin down the requirement - what period are you
talking about, what calculations do you want to do, what are the relevant
days (and in international trading systems, for example, even working that
out is a black art, or used to be) and so on. Then you've got to decide how
to implement that requirement. Even if you've identified a really simple
requirement there's a maintenance issue, i.e. you may have to modify the
holiday dates. That means you have to remember to do it, and you have to
remember what to look at. Even if you have one piece of code that does what
you need and you have a strict timetable for reviewing it, if you re-use
that code by copying it the maintenance problem will swiftly get out of
hand. Personally I favour a database-oriented approach where you
a. keep all the date-related info. you want, as far ahead as you choose, in
a database
b. you implement the algorithms you need in the database, e.g. as stored
procedures or functions
c. you provide the interfaces you need for the software that needs to do
these calculations.
Obviously, you still have to remember to review your dates, and you have to
maintain the data. But at least everything that changes is then in one
place, and if your code is properly designed and written, none of the
software that uses the date-related functions need change over time.
FWIW I had a look around for "web services" that provide this kind of
facility - I didn't find any obvious candidates, but that doesn't mean they
don't exist. There are certainly several web sites with extensive lists of
public holidays.
Peter Jamieson
The reason I would always opt for a database-based solution except in the
simplest cases is because
and when can you pin them down
when are the holidays and when do you get to know where, if for no other
reason than you don't know when the holdisays w. You have to pin down the
requirement.
You really have to use an approach based on an external database"Greg

Post by Greg Maxey
All confusion cleared up. I never read in this post until I saw your
method as I usually avoid the volleys between Jezebel and macropod ;-).
I tried something from scratch over a year ago that failed miserably.
If
my code is elegant it is only because I saw it in the rough gem that you
posted. I saw almost instantly that you had a good idea, but that you
would eventually push a holiday into a weekend.
My comment about it being too complex for me to tackle is related to a
total solution, i.e., a macro that would work today, next year, last year,
next century, etc.without periodic ajustments. I don't know how to sovle
that.
For your purposes, I think you have a very workable solution.
I don't know if it is something you need, but if you are interested in
future, past dates, date sequences, date spans, etc. you might look at my
date sequencer Addin.
http://gregmaxey.mvps.org/Date_Sequencer.htm
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
Just let me clear up some confusion about your second post back; so
long
as
the user added 12/21/07 & 1/1/08 to the code, the problem you cite about
entering December 30, 2007 is resolved. Given the original poster's interest
in creating a macro that would post a date only two days later than the
document itself, I felt that most of the problems with encountering dates
beyond the macro's reach could be solved by yearly maintenance.
For instance, I use a calculation program to determine a compensation rate
that changes yearly, with certain minimums and maximums mandated, so I must
revise my calculation program on an annual basis. The new
compensation
rate
is published only about six months before it takes effect, so I revise the
program as soon as the rates are published.
I was interested in this topic because I have been working on a similar
document that would require calculation of a number of dates backwards from
the initial date entered. As with the original poster, I wanted to avoid
weekends and holidays. I figured out the weekends pretty easily, but the
work developing code to avoid holidays really highlighted the danger in
continuing to use the logic that solved the simpler problem of avoiding
weekends--it made the code much more convoluted. That's why I had started
using the Select Case method (already used in my calculation program), but I
wasn't as comfortable using True-False statements as I would like.
Your
solution does seem "elegant" when compared with the approach I was taking.
BTW, some of the dates I'll enter and calculate back from go well
beyond
the
two days that were the concern of the original poster, and thinking about
this issue in this context has made me realize that I probably need to revise
the code quarterly rather than annually to insure I don't hit snags.
Given
the amount of time my secretary and I will save using this macro, however, I
guarantee you the two minutes researching the dates of the next quarter's
holidays and the two minutes revising the code are well worth it!

Post by lwildernorva
Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or
Holiday(pDueDate)
= True pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#,
#11/12/2007#, # _
11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
Here is the problem that I see. If your user enters December 30,
2007 the code will return 1/01/2008. We all know that is a holiday.
......
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found
a flaw with this macro. A date generated in 08 may fall on a
holiday." .....
Which illustrates IMHO the complexity of this approach and certainly
illustrates that I am not up to solving it. Good luck.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

...
read more »- Hide quoted text -
- Show quoted text -

macropod

2007-03-04 00:47:00 UTC

Permalink

Hi Graham,

Ponder this:
{QUOTE
{SET % Change the delay to whatever you want}
{SET Delay 14}
{SET a{=INT((14-{DATE \@ M})/12)}}
{SET b{={DATE \@ yyyy}+4800-a}}
{SET c{={DATE \@ M}+12*a-3}}
{SET d{DATE \@ d}}
{SET % Here we have the calculated jd# for the delay}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET % Here we get the calculated year and convert that to a jd#}
{SET yr{=100*e+g-4800+INT(i/10)}}
{SET yb{=yr+4799}}
{SET yjd{=365*yb+INT(yb/4)-INT(yb/100)+INT(yb/400)-31739}}
{SET % test whether the calculated year is a leap year}
{SET LpYr{=(MOD(yr,4)=0)-(MOD(yr,400)=0)+(MOD(yr,100)=0)}}
{SET % Preset holidays are input as their annual day number in a normal year. We then add the yjd#, plus the leap year offset for
holidays that fall after 28 Feb, to get the holiday's jd# for the calculated year}
{SET NewYr {=1+yjd}}
{SET AusDay {=26+yjd}}
{SET ANZAC {=115+LpYr+yjd}}
{SET Christ{=359+LpYr+yjd}}
{SET Boxing{=Christ+1}}
{SET % Here we calculate the date of Easter for the calculated year}
{SET ea{=MOD(yr,19)}}
{SET eb{=INT(yr/100)}}
{SET ec{=MOD(yr,100)}}
{SET ed{=INT(eb/4)}}
{SET ee{=MOD(eb,4)}}
{SET ef{=(eb+8)/25}}
{SET eg{=INT((eb-ef+1)/3)}}
{SET eh{=MOD((19*ea+eb-ed-eg+15),30)}}
{SET ei{=INT(ec/4)}}
{SET ek{=MOD(ec,4)}}
{SET el{=MOD((32+2*ee+2*ei-eh-ek),7)}}
{SET em{=INT((ea+11*eh+22*el)/451)}}
{SET emth{=INT((eh+el-7*em+114)/31)}}
{SET eday{=MOD((eh+el-7*em+114),31)+1}}
{SET % Now we calculate Easter Sunday's day number for the year, from which we can get the day numbers for Good Friday and Easter
Monday.}
{SET Easter{=eday+INT((emth-0.986)*30.575)-2+LpYr}}
{SET GdFri{=Easter-2+yjd}}
{SET EMon{=Easter+1+yjd}}
{SET % Herew we calculate Labor Day - the 1st Monday in October}
{SET LM 10}
{SET LW 1}
{SET LD 0}
{SET La{=INT((14-MOD(LM+11,12)-1)/12)}}
{SET Lb{=yr+4800-La+INT(LM/13)}}
{SET Lc{=MOD(LM+11,12)+1+12*La-3}}
{SET LJD{=1+INT((153*c+2)/5)+365*Lb+INT(Lb/4)-INT(Lb/100)+INT(Lb/400)-32045}}
{SET Limit{=IF((LM=2),28+((MOD(yr,4)=0)+(MOD(yr,400)=0)-(MOD(yr,100)=0)),IF((LM=4)+(LM=6)+(LM=9)+(LM=11)=1,30,31))}}
{SET
LJD{=INT(LJD/7+MAX(LW-1,0))*7+LD+(INT(LJD/7)*7+LD<LJD)*7-((INT(LJD/7+LW-1)*7+LD+(INT(LJD/7)*7+LD<LJD)*7>LJD+Limit-1)*INT((INT(LJD/7+LW-1)*7+LD+(INT(LJD/7)*7+LD<LJD)*7-(LJD+Limit))/7+1)*7)}}
{SET % Now that we've got all the holidays, we can add a day if the calculated date falls on a holiday, and extra days if the
holiday falls on a weekend. We also allow an extra day (for the Boxing Day holiday) if the calculated date falls on Christmas Day
(more still if he calculated date falls on a Christmas Day Friday, Saturday or Sunday - likewise if the calculated date falls on a
Saturday, Sunday or Monday Boxing Day}
{SET
Adj{={=NewYr=jd}+{=(NewYr=jd)*(MOD(NewYr,7)>4)*(7-MOD(NewYr,7))}+{=AusDay=jd}+{=(AusDay=jd)*(MOD(AusDay,7)>4)*(7-MOD(AusDay,7))}+{=GdFri=jd}+{=ANZAC=jd}+{=(ANZAC=jd)*(MOD(ANZAC,7)>4)*(7-MOD(ANZAC,7))}+{=Christ=jd}*2+{=(Christ=jd)*((MOD(Christ,7)>3)*2-(MOD(Christ,7)=6)}+{=Boxing=jd}+{=(Boxing=jd)*((MOD(Boxing,7)>4)*2+MOD(Boxing,7)=0))}}}
{SET jd{=Adj+jd}}
{SET % For the general case, and any holiday adjustments, adjust to the following Monday if the calculated date falls on a weekend}
{SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
{SET % Now add a day if the revised date falls on a holiday Monday (other than Christmas Day or Boxing Day, which we've already
dealt with).}
{SET Adj{={=NewYr=jd}+{=AusDay=jd}+{=EMon=jd}+{=ANZAC=jd}+{=LJD=jd}}}
{SET jd{=Adj+jd}}
{SET % We can now complete the calculation}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
"{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}

The above field is coded to handle a variety of Australian Holidays, some of which are on fixed dates (except when they fall on
weekends and get shoved to the following Monday), Easter (which moves every year), and Labour Day, which falls on the first Monday
in October (where I am at least). Even the Christmas & Boxing day pairing is handled.

Just shows it can be done - without needing either vba, a holiday database, or recoding every year. And all in just 58 lines of code
(the SET % statements are just comments)

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by lwildernorva
Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or Holiday(pDueDate) = True
pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#, #11/12/2007#, # _
11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#
Holiday = True
Case Else
Holiday = False
End Select
End Function
Here is the problem that I see. If your user enters December 30, 2007 the code will return 1/01/2008. We all know that is a
.....
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found a flaw with this macro. A date generated in 08 may fall on a
holiday."
....
Which illustrates IMHO the complexity of this approach and certainly illustrates that I am not up to solving it. Good luck.
--
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
This method should work for adding two days but skipping the weekend
days. A bookmark and a macro should do the trick. Create your
letter template. My simple template says "We would like the
courtesy of a reply by []" where the brackets are replaced by an
inserted bookmark. For the purposes of this question, I called the
bookmark, "bkSecondDate" which represents the two days in the
future. The following code should automatically insert the advanced
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
This code can be embedded in the template as a macro and fired to
run when a new document based on the template is created by using
the AutoNew command.
Apparently without reference to an external database, to avoid
holidays, you would need to create additional coding that compares
the date returned with a set of dates to avoid, in this case annual
holidays. This coding would have to be revised annually in order to
include those holidays that change from year to year, such as Easter
and Thanksgiving. I haven't yet generated this code, but if I have
time to come up with something, I'll post back.
Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this method
for any serious purpose.

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and
holidays thing) you can try using a DATABASE field, but you need
a Jet database to do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,datevalue('{
or even
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert using ctrl-F9.
For earlier versions of Word you need a bit more to prevent Word
trying to open Access (which you may not have).
You may also encounter a number of security-related barriers to this approach.
The .mdb needs to be a valid Jet database, but it can be empty,
i.e. have no visible queries or tables. There are various ways
a. If you have Access, it should be easy to create an empty .mdb.
b. Depending on what software is on your system you may be able
to right-click in Windows Explorer and use the "New" option to
create a new "Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to
start the Mail Merge Wizard, follow the task panes, and in
"Select Recipients", select "Type a new list", then click
Create. Add a record, then save the resulting .mdb
Whether it's advisable to use this approach is a different
question. The main drawback of is its reliance on an external
file (and related database access software) and the increased
potential for maintenance problems.
To deal with weekends you could use a more complicated
expression, e.g. something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
"SELECT dateadd('d',choose(weekday('{ d }'),2,2,2,2,4,4,3),'{ d
NB in recent versions of Word, the result of a DATABASE field
sometimes includes a paragraph mark, which has rendered it
useless for including individual text values. If the result is a
date or a number, a date/numeric format switch seems to deal
with that problem.
As for the holidays, even assuming you are only interested in one
country's holidays, you would only be able to build additional
query criteria in if the list of holidays was atypically small
and predictable (partly because the query text length is
constrained, probably to 255 characters). Typically, you will
simply have to maintain a calendar of some kind. If you also
keep that in a database (somewhere, depends on what sort of
system we're talking about) then you have to decide how far
ahead your calendar is going to go, and precisely how you are
going to represent holidays, weekends and so on. If you are only
ever going to have a "two business days ahead" requirement, you
might as well have a database with two columns containing the
currentdate,dateplus2
2007-02-24,2007-02-27
Then you would need
{ DATABASE \d "your database path name" \s "SELECT dateplus2 FROM
You could even use a Word document to contain such a database if
it weren't for the fact that Word doesn't consider the result to
be a date, so doesn't apply the date format, so may insert that
extra paragraph I mentioned.
Peter Jamieson

Graham Mayor

2007-03-04 07:44:23 UTC

Permalink

Gulp!
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Post by macropod
Hi Graham,
{QUOTE
{SET % Change the delay to whatever you want}
{SET Delay 14}
{SET % Here we have the calculated jd# for the delay}
{SET
jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
{SET e{=INT((4*(jd+32044)+3)/146097)}} {SET
f{=jd+32044-INT(146097*e/4)}} {SET g{=INT((4*f+3)/1461)}}
{SET % Here we get the calculated year and convert that to a jd#}
{SET yr{=100*e+g-4800+INT(i/10)}}
{SET yb{=yr+4799}}
{SET yjd{=365*yb+INT(yb/4)-INT(yb/100)+INT(yb/400)-31739}}
{SET % test whether the calculated year is a leap year}
{SET LpYr{=(MOD(yr,4)=0)-(MOD(yr,400)=0)+(MOD(yr,100)=0)}}
{SET % Preset holidays are input as their annual day number in a
normal year. We then add the yjd#, plus the leap year offset for
holidays that fall after 28 Feb, to get the holiday's jd# for the
calculated year} {SET NewYr {=1+yjd}} {SET AusDay {=26+yjd}}
{SET ANZAC {=115+LpYr+yjd}}
{SET Christ{=359+LpYr+yjd}}
{SET Boxing{=Christ+1}}
{SET % Here we calculate the date of Easter for the calculated year}
{SET ea{=MOD(yr,19)}}
{SET eb{=INT(yr/100)}}
{SET ec{=MOD(yr,100)}}
{SET ed{=INT(eb/4)}}
{SET ee{=MOD(eb,4)}}
{SET ef{=(eb+8)/25}}
{SET eg{=INT((eb-ef+1)/3)}}
{SET eh{=MOD((19*ea+eb-ed-eg+15),30)}}
{SET ei{=INT(ec/4)}}
{SET ek{=MOD(ec,4)}}
{SET el{=MOD((32+2*ee+2*ei-eh-ek),7)}}
{SET em{=INT((ea+11*eh+22*el)/451)}}
{SET emth{=INT((eh+el-7*em+114)/31)}}
{SET eday{=MOD((eh+el-7*em+114),31)+1}}
{SET % Now we calculate Easter Sunday's day number for the year, from
which we can get the day numbers for Good Friday and Easter Monday.}
{SET Easter{=eday+INT((emth-0.986)*30.575)-2+LpYr}}
{SET GdFri{=Easter-2+yjd}}
{SET EMon{=Easter+1+yjd}}
{SET % Herew we calculate Labor Day - the 1st Monday in October}
{SET LM 10}
{SET LW 1}
{SET LD 0}
{SET La{=INT((14-MOD(LM+11,12)-1)/12)}}
{SET Lb{=yr+4800-La+INT(LM/13)}}
{SET Lc{=MOD(LM+11,12)+1+12*La-3}}
{SET
LJD{=1+INT((153*c+2)/5)+365*Lb+INT(Lb/4)-INT(Lb/100)+INT(Lb/400)-32045}}
{SET
Limit{=IF((LM=2),28+((MOD(yr,4)=0)+(MOD(yr,400)=0)-(MOD(yr,100)=0)),IF((LM=4)+(LM=6)+(LM=9)+(LM=11)=1,30,31))}}
{SET
LJD{=INT(LJD/7+MAX(LW-1,0))*7+LD+(INT(LJD/7)*7+LD<LJD)*7-((INT(LJD/7+LW-1)*7+LD+(INT(LJD/7)*7+LD<LJD)*7>LJD+Limit-1)*INT((INT(LJD/7+LW-1)*7+LD+(INT(LJD/7)*7+LD<LJD)*7-(LJD+Limit))/7+1)*7)}}
{SET % Now that we've got all the holidays, we can add a day if the
calculated date falls on a holiday, and extra days if the holiday
falls on a weekend. We also allow an extra day (for the Boxing Day
holiday) if the calculated date falls on Christmas Day (more still if
he calculated date falls on a Christmas Day Friday, Saturday or
Sunday - likewise if the calculated date falls on a Saturday, Sunday
or Monday Boxing Day} {SET
Adj{={=NewYr=jd}+{=(NewYr=jd)*(MOD(NewYr,7)>4)*(7-MOD(NewYr,7))}+{=AusDay=jd}+{=(AusDay=jd)*(MOD(AusDay,7)>4)*(7-MOD(AusDay,7))}+{=GdFri=jd}+{=ANZAC=jd}+{=(ANZAC=jd)*(MOD(ANZAC,7)>4)*(7-MOD(ANZAC,7))}+{=Christ=jd}*2+{=(Christ=jd)*((MOD(Christ,7)>3)*2-(MOD(Christ,7)=6)}+{=Boxing=jd}+{=(Boxing=jd)*((MOD(Boxing,7)>4)*2+MOD(Boxing,7)=0))}}}
{SET jd{=Adj+jd}} {SET % For the general case, and any holiday
adjustments, adjust to the following Monday if the calculated date
falls on a weekend} {SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}} {SET %
Now add a day if the revised date falls on a holiday Monday (other
than Christmas Day or Boxing Day, which we've already dealt with).}
{SET Adj{={=NewYr=jd}+{=AusDay=jd}+{=EMon=jd}+{=ANZAC=jd}+{=LJD=jd}}}
{SET jd{=Adj+jd}} {SET % We can now complete the calculation} {SET
e{=INT((4*(jd+32044)+3)/146097)}} {SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}} {SET h{=f-INT(1461*g/4)}} {SET
i{=INT((5*h+2)/153)}} {SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
The above field is coded to handle a variety of Australian Holidays,
some of which are on fixed dates (except when they fall on weekends
and get shoved to the following Monday), Easter (which moves every
year), and Labour Day, which falls on the first Monday in October
(where I am at least). Even the Christmas & Boxing day pairing is
handled.
Just shows it can be done - without needing either vba, a holiday
database, or recoding every year. And all in just 58 lines of code
(the SET % statements are just comments)
Cheers

Post by lwildernorva
Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or Holiday(pDueDate)
= True pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#,
#11/12/2007#, # _ 11/22/2007#, #11/23/2007#, #12/24/2007#,
#12/25/2007# Holiday = True
Case Else
Holiday = False
End Select
End Function
Here is the problem that I see. If your user enters December 30,
2007 the code will return 1/01/2008. We all know that is a holiday.
You could add something like: .....
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found
a flaw with this macro. A date generated in 08 may fall on a
holiday." ....
Which illustrates IMHO the complexity of this approach and certainly
illustrates that I am not up to solving it. Good luck. --
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
First quick stab at revising what I did earlier. I don't think it's
very efficient, but I'm looking for a better solution. The
Memorial Day, July 4th, Labor Day, Columbus Day, Veterans Day,
Thanksgiving Day and the Friday after Thanksgiving Day, Christmas
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
Dim AvoidDate As Date
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
Do While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #5/28/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #7/4/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #9/3/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #10/15/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/12/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/22/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/23/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/24/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/25/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/31/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #1/1/2008#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
As mentioned in my first message, the disadvantage to this approach
is that you must revise each holiday annually, both to adjust the
holidays that change dates from year to year and to adjust the year
for each holiday. On the other hand, if you don't have access to an
external database of holidays and store this macro in the template
for your document so that it is easily accessible, this method won't
require that much work each year.
I'll try to revise this code to make it more efficient, but I had
been thinking about this problem in connection with another
document I've been working on so it gave me the motivation to
tackle it this afternoon.

Post by lwildernorva
This method should work for adding two days but skipping the
weekend days. A bookmark and a macro should do the trick. Create
your letter template. My simple template says "We would like the
courtesy of a reply by []" where the brackets are replaced by an
inserted bookmark. For the purposes of this question, I called the
bookmark, "bkSecondDate" which represents the two days in the
future. The following code should automatically insert the
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
This code can be embedded in the template as a macro and fired to
run when a new document based on the template is created by using
the AutoNew command.
Apparently without reference to an external database, to avoid
holidays, you would need to create additional coding that compares
the date returned with a set of dates to avoid, in this case annual
holidays. This coding would have to be revised annually in order
to include those holidays that change from year to year, such as
Easter and Thanksgiving. I haven't yet generated this code, but
if I have time to come up with something, I'll post back.
Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this
method for any serious purpose.

Post by macropod
Hi Peter/Graham,
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
to cope with weekends would be fairly trivial. For example,
adding: {SET jd{=jd+(MOD(jd,7)>4)*(4-MOD(jd,7))}}
or
{SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
after the existing 'SET' jd field would adjust the calculated
dates to the previous Friday or next Monday, respectively.
Dealing with public holidays could be problematic if they fall
on Mondays or Fridays, but only because they'd have to be coded
for on a case-by-case basis. VBA probably wouldn't be any
easier in this regard.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and
holidays thing) you can try using a DATABASE field, but you
need a Jet database to do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert using ctrl-F9.
For earlier versions of Word you need a bit more to prevent
Word trying to open Access (which you may not have).
You may also encounter a number of security-related barriers to
this approach.
The .mdb needs to be a valid Jet database, but it can be empty,
i.e. have no visible queries or tables. There are various ways
a. If you have Access, it should be easy to create an empty
.mdb. b. Depending on what software is on your system you may
be able to right-click in Windows Explorer and use the "New"
option to create a new "Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it
to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to
start the Mail Merge Wizard, follow the task panes, and in
"Select Recipients", select "Type a new list", then click
Create. Add a record, then save the resulting .mdb
Whether it's advisable to use this approach is a different
question. The main drawback of is its reliance on an external
file (and related database access software) and the increased
potential for maintenance problems.
To deal with weekends you could use a more complicated
expression, e.g. something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
\s "SELECT dateadd('d',choose(weekday('{ d
NB in recent versions of Word, the result of a DATABASE field
sometimes includes a paragraph mark, which has rendered it
useless for including individual text values. If the result is
a date or a number, a date/numeric format switch seems to deal
with that problem.
As for the holidays, even assuming you are only interested in
one country's holidays, you would only be able to build
additional query criteria in if the list of holidays was
atypically small and predictable (partly because the query
text length is constrained, probably to 255 characters).
Typically, you will simply have to maintain a calendar of some
kind. If you also keep that in a database (somewhere, depends
on what sort of system we're talking about) then you have to
decide how far ahead your calendar is going to go, and
precisely how you are going to represent holidays, weekends
and so on. If you are only ever going to have a "two business
days ahead" requirement, you might as well have a database
currentdate,dateplus2
2007-02-24,2007-02-27
Then you would need
{ DATABASE \d "your database path name" \s "SELECT dateplus2
You could even use a Word document to contain such a database
if it weren't for the fact that Word doesn't consider the
result to be a date, so doesn't apply the date format, so may
insert that extra paragraph I mentioned.
Peter Jamieson

Post by spartacus3
In Word, I need to create a field that inserts a future date two business
days ahead of the current date. I suspect it involves { DATE
and +2 somehow. I'm still not sure how to make it skip
weekends and holidays.
Does anybody have any ideas?

Perry

2007-03-04 13:19:22 UTC

Permalink

whoooshhh....
--
--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

Post by Graham Mayor
Gulp!
--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP
My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Post by lwildernorva
Sub AddDate()
Dim pDueDate As Date
Dim oRng As Word.Range
On Error GoTo Err_Handler
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
Do While DatePart("w", pDueDate, vbMonday) > 5 Or Holiday(pDueDate)
= True pDueDate = DateAdd("d", 1, pDueDate)
Loop
Set oRng = ActiveDocument.Bookmarks("DueDate").Range
oRng.Text = CStr(pDueDate)
ActiveDocument.Bookmarks.Add "DueDate", oRng
Exit Sub
MsgBox "Please enter a valid date format."
Resume Err_ReEntry
End Sub
Function Holiday(pDate As Date) As Boolean
Select Case pDate
Case #5/28/2007#, #7/4/2007#, #9/3/2007#, #10/15/2007#,
#11/12/2007#, # _ 11/22/2007#, #11/23/2007#, #12/24/2007#,
#12/25/2007# Holiday = True
Case Else
Holiday = False
End Select
End Function
Here is the problem that I see. If your user enters December 30,
2007 the code will return 1/01/2008. We all know that is a holiday.
You could add something like: .....
pDueDate = DateAdd("d", 2, InputBox("Enter date: "))
If Right(CStr(pDueDate), 2) = "08" Then MsgBox "You have just found
a flaw with this macro. A date generated in 08 may fall on a
holiday." ....
Which illustrates IMHO the complexity of this approach and certainly
illustrates that I am not up to solving it. Good luck. --
Greg Maxey/Word MVP
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Post by lwildernorva
First quick stab at revising what I did earlier. I don't think it's
very efficient, but I'm looking for a better solution. The
Memorial Day, July 4th, Labor Day, Columbus Day, Veterans Day,
Thanksgiving Day and the Friday after Thanksgiving Day, Christmas
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
Dim AvoidDate As Date
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
Do While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #5/28/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #7/4/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #9/3/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #10/15/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/12/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/22/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #11/23/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/24/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/25/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #12/31/2007#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Do While strSecondDate = #1/1/2008#
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
As mentioned in my first message, the disadvantage to this approach
is that you must revise each holiday annually, both to adjust the
holidays that change dates from year to year and to adjust the year
for each holiday. On the other hand, if you don't have access to an
external database of holidays and store this macro in the template
for your document so that it is easily accessible, this method won't
require that much work each year.
I'll try to revise this code to make it more efficient, but I had
been thinking about this problem in connection with another
document I've been working on so it gave me the motivation to
tackle it this afternoon.

Post by lwildernorva
This method should work for adding two days but skipping the
weekend days. A bookmark and a macro should do the trick. Create
your letter template. My simple template says "We would like the
courtesy of a reply by []" where the brackets are replaced by an
inserted bookmark. For the purposes of this question, I called the
bookmark, "bkSecondDate" which represents the two days in the
future. The following code should automatically insert the
Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer
Dim bkRange As Range
strFirstDate = InputBox("Enter Beginning Date in Full Date Format,
i.e., 'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)
While DatePart("w", strSecondDate, vbMonday) > 5
strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Wend
Set bkRange = ActiveDocument.Bookmarks("bkSecondDate").Range
bkRange.Text = strSecondDate
End Sub
This code can be embedded in the template as a macro and fired to
run when a new document based on the template is created by using
the AutoNew command.
Apparently without reference to an external database, to avoid
holidays, you would need to create additional coding that compares
the date returned with a set of dates to avoid, in this case annual
holidays. This coding would have to be revised annually in order
to include those holidays that change from year to year, such as
Easter and Thanksgiving. I haven't yet generated this code, but
if I have time to come up with something, I'll post back.
Hope this is helpful.

Post by macropod
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Jezebel
That explains pretty clearly why you don't want to use this
method for any serious purpose.

Post by macropod
Hi Peter/Graham,
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
to cope with weekends would be fairly trivial. For example,
adding: {SET jd{=jd+(MOD(jd,7)>4)*(4-MOD(jd,7))}}
or
{SET jd{=jd+(MOD(jd,7)>4)*(7-MOD(jd,7))}}
after the existing 'SET' jd field would adjust the calculated
dates to the previous Friday or next Monday, respectively.
Dealing with public holidays could be problematic if they fall
on Mondays or Fridays, but only because they'd have to be coded
for on a case-by-case basis. VBA probably wouldn't be any
easier in this regard.
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Post by Peter Jamieson
There's no really simple way to do any of what you want using fields.
For the simple case (i.e. forgetting about the weekends and
holidays thing) you can try using a DATABASE field, but you
need a Jet database to do it e.g. in Word 2002/2003
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,'{ DATE
where /all/ the {} are the special field braces you can insert
using ctrl-F9.
For earlier versions of Word you need a bit more to prevent
Word trying to open Access (which you may not have).
You may also encounter a number of security-related barriers to
this approach.
The .mdb needs to be a valid Jet database, but it can be empty,
i.e. have no visible queries or tables. There are various ways
a. If you have Access, it should be easy to create an empty
.mdb. b. Depending on what software is on your system you may
be able to right-click in Windows Explorer and use the "New"
option to create a new "Micrrosoft Office Access Application"
c. create one in a copy of Access on another system and copy it
to your system.
d. in Word, use Tools|"Letters and Mailings"|"Mail Merge" to
start the Mail Merge Wizard, follow the task panes, and in
"Select Recipients", select "Type a new list", then click
Create. Add a record, then save the resulting .mdb
Whether it's advisable to use this approach is a different
question. The main drawback of is its reliance on an external
file (and related database access software) and the increased
potential for maintenance problems.
To deal with weekends you could use a more complicated
expression, e.g. something like
{ DATABASE \d "c:\i\i.mdb" \s "SELECT
dateadd('d',choose(weekday(datevalue('{ DATE
or, paring it down a bit, perhaps
\s "SELECT dateadd('d',choose(weekday('{ d
NB in recent versions of Word, the result of a DATABASE field
sometimes includes a paragraph mark, which has rendered it
useless for including individual text values. If the result is
a date or a number, a date/numeric format switch seems to deal
with that problem.
As for the holidays, even assuming you are only interested in
one country's holidays, you would only be able to build
additional query criteria in if the list of holidays was
atypically small and predictable (partly because the query
text length is constrained, probably to 255 characters).
Typically, you will simply have to maintain a calendar of some
kind. If you also keep that in a database (somewhere, depends
on what sort of system we're talking about) then you have to
decide how far ahead your calendar is going to go, and
precisely how you are going to represent holidays, weekends
and so on. If you are only ever going to have a "two business
days ahead" requirement, you might as well have a database
currentdate,dateplus2
2007-02-24,2007-02-27
Then you would need
{ DATABASE \d "your database path name" \s "SELECT dateplus2
You could even use a Word document to contain such a database
if it weren't for the fact that Word doesn't consider the
result to be a date, so doesn't apply the date format, so may
insert that extra paragraph I mentioned.
Peter Jamieson

Continue reading on narkive:

Search results for 'How do insert an automatic future date in Word?' (Questions and Answers)

replies

Why is it that people feel less....?

started 2011-02-06 20:37:14 UTC

hunting

replies

Homemade invitations?

started 2006-08-30 13:38:09 UTC

do it yourself (diy)

replies

What subjects should I take in higher schooling so as to become a Hotel Management personal?