macropod said: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]
-------------------------
Peter Jamieson said: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
Jezebel said:That explains pretty clearly why you don't want to use this method for any
serious purpose.
macropod said: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]
-------------------------
Peter Jamieson said: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
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?
macropod said:OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------
Jezebel said:That explains pretty clearly why you don't want to use this method for any
serious purpose.
macropod said: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]
-------------------------
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
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?
lwildernorva said: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.
macropod said:OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------
Jezebel said:That explains pretty clearly why you don't want to use this method for any
serious purpose.
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]
-------------------------
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
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?
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.
lwildernorva said: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.
macropod said:OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------
That explains pretty clearly why you don't want to use this method
for any serious purpose.
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]
-------------------------
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
message
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?
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.
lwildernorva said: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.
macropod said:OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------
That explains pretty clearly why you don't want to use this method
for any serious purpose.
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]
-------------------------
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
message
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?
Greg Maxey said: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.
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.
lwildernorva said: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.
:
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------
That explains pretty clearly why you don't want to use this method
for any serious purpose.
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]
-------------------------
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
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.
Greg Maxey said: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.
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.
:
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.
:
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------
That explains pretty clearly why you don't want to use this
method for any serious purpose.
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]
-------------------------
message 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
Greg Maxey said: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.
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.
Greg Maxey said: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.
lwildernorva wrote:
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.
:
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.
:
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------
That explains pretty clearly why you don't want to use this
method for any serious purpose.
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]
-------------------------
message 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.
lwildernorva said: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!
Greg Maxey said: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.
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.
:
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.
lwildernorva wrote:
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.
:
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.
:
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------
That explains pretty clearly why you don't want to use this
method for any serious purpose.
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]
-------------------------
message 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.
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.
Greg Maxey said: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.
lwildernorva said: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!
Greg Maxey said: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.
lwildernorva wrote:
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.
:
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.
lwildernorva wrote:
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.
:
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.
:
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------
That explains pretty clearly why you don't want to use this
method for any serious purpose.
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]
-------------------------
message 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.
Greg Maxey said: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.
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.
lwildernorva said: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.
:
OK, so how about a serious vba solution ...
--
macropod
[MVP - Microsoft Word]
-------------------------
That explains pretty clearly why you don't want to use this method
for any serious purpose.
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]
-------------------------
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
message
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?
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.