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