How do insert an automatic future date in Word?

[spartacus3]

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

[Jezebel]

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

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

"spartacus3" <spart...@discussions.microsoft.com> wrote in message
news:57CCCFF4-DD0E-4C25...@microsoft.com...

[Peter Jamieson]

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

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

{ DATABASE \d "c:\i\i.mdb" \s "SELECT dateadd('d',2,datevalue('{ DATE
\@"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

"spartacus3" <spart...@discussions.microsoft.com> wrote in message
news:57CCCFF4-DD0E-4C25...@microsoft.com...

[Graham Mayor]

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

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

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

[macropod]

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" <p...@KillmapSpjjnet.demon.co.uk> wrote in message news:e4R5Av$VHHA...@TK2MSFTNGP02.phx.gbl...

[Jezebel]

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

"macropod" <inv...@invalid.invalid> wrote in message
news:eTsNBoX...@TK2MSFTNGP06.phx.gbl...

[macropod]

OK, so how about a serious vba solution ...

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

"Jezebel" <warc...@whitehouse.gov> wrote in message news:OE3vu9XW...@TK2MSFTNGP04.phx.gbl...

[lwildernorva]

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

[lwildernorva]

First quick stab at revising what I did earlier. I don't think it's very
efficient, but I'm looking for a better solution. The 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.

[Greg Maxey]

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

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

Sub AddDate()
Dim strFirstDate As String
Dim strSecondDate As Date
Dim IntervalType As String
Dim Number As Integer

strFirstDate = InputBox("Enter Beginning Date in Full Date Format, i.e.,
'November 1, 2007'")
IntervalType = "d"
Number = 2
strSecondDate = DateAdd(IntervalType, Number, strFirstDate)

Do While DatePart("w", strSecondDate, vbMonday) > 5 Or
Holiday(strSecondDate) = True

strSecondDate = DateAdd(IntervalType, 1, strSecondDate)
Loop

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

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

[Greg Maxey]

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]

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" wrote:

> ......

> 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."

> .....

[Greg Maxey]

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

[lwildernorva]

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

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

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

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

[Greg Maxey]

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

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

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

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

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

http://gregmaxey.mvps.org/Date_Sequencer.htm

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

"lwildernorva" <lwilde...@discussions.microsoft.com> wrote in message
news:0CD61008-1E6F-4380...@microsoft.com...

[Peter Jamieson]

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

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

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

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

Peter Jamieson

The reason I would always opt for a database-based solution except in the
simplest cases is because
and when can you pin them down
when are the holidays and when do you get to know where, if for no other
reason than you don't know when the holdisays w. You have to pin down the
requirement.
You really have to use an approach based on an external database
"Greg Maxey" <gma...@mvps.oSCARrOMEOgOLF> wrote in message
news:OkgZVnFX...@TK2MSFTNGP03.phx.gbl...

[Greg Maxey]

Peter,

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

On Mar 2, 5:09 am, "Peter Jamieson" <p...@KillmapSpjjnet.demon.co.uk>
wrote:

> > "lwildernorva" <lwilderno...@discussions.microsoft.com> wrote in message

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

[Peter Jamieson]

Greg,

<<
I just got interested in the VBA code and did my
best.
>>

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

Peter Jamieson

"Greg Maxey" <gma...@gmail.com> wrote in message
news:1172841987.6...@64g2000cwx.googlegroups.com...

[Doug Robbins - Word MVP]

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

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

--
Hope this helps.

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

Doug Robbins - Word MVP

"Peter Jamieson" <p...@KillmapSpjjnet.demon.co.uk> wrote in message

news:%23P0cl9P...@TK2MSFTNGP03.phx.gbl...

[macropod]

Hi Graham,

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

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

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

Cheers

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

"Greg Maxey" <gma...@mvps.oSCARrOMEOgOLF> wrote in message news:OEkRg44W...@TK2MSFTNGP02.phx.gbl...

[Graham Mayor]

Gulp!

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

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

[Perry]

whoooshhh....

--
--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

"Graham Mayor" <gma...@REMOVETHISmvps.org> schreef in bericht
news:Oz2NkDjX...@TK2MSFTNGP05.phx.gbl...