Holiday Table Design

On Sun, 4 Oct 2009 10:02:01 -0700, Klatuu
wrote:

Actually, John, I did this once for a purpose. It was for an Electic energy
company.

Saturdays were typically Non Working days, but about 3 or 4 times a year,
depending on a weird meter reading cycle, Saturday became a work day and
those days had to be used in the calculation.

Good point! It would certainly give more flexibility.

--

John W. Vinson [MVP]

On Sun, 4 Oct 2009 10:40:01 -0700, Billiam
wrote:

Thank you for responding, Klatuu!

So all the dates would be in the same Holiday table?

Do you know if anyone has written a routine to autopopulate Holidays of a
fixed day? I suppose I could just enter the list in excel of those Holidays
whoose day does not change, just the year, and autofil them for a decade or
so and then imoprt them to the Holiday table...does this seem reasonable?

Autofill in Excel followed by copy and paste would be even faster than writing
a query or code to do so.

What's trickier than the 4th of July or other fixed-date holidays are the
"first Monday in September" or "Fourth Thursday in November" type things. I
could spend fifteen minutes writing and testing code to do this... or ten
minutes with the Outlook calendar in one window and an Access table or form in
another, filling it in by hand! Remember, if you fill out ten years' of
holidays, it's probably a one-time operation and who knows what technology
will be around in 2019 to take it from there.

Thanks again for your help! It was fortunate that you mentioned the Electric
Company scenario, as I had forgotten there are 2 occassions when we have to
work Saturday, which is not a normal work day...

Thank you again for your continued help! I would have thrown Access out
without this forum, and very kind and knowledgable volunteers.
Thanks for shining a light for those of us stumbling around in the dark! For
me, Access is like a bad addiction, you know it's bad for your health (for me
frustration and stress) but the results are SO WORTH IT, if you can design
what you need...

Yup. That's why we all love (and hate) it so much...!
--

John W. Vinson [MVP]

"Steve" wrote in message
...
Hello Billiam,

I would like to offer to design and populate a holiday table for you. I
provide help with Access, Excel and Word applications for a small fee. My
fee to help you would be very modest. If you are interested, contact me.

Steve



So what is it now, you are back from hiding and you are up to six pimping
posts and nothing that qualifies as free help?



These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the past Christmas
period and a few gems from the Access newsgroups to show Stevie's
"expertise".


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Steve wrote:
Yes, you are right but a database is the correct tool to use not a
spreadsheet.


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...


Sept 10, 2009
(In respose to a perfectly adequate GENERIC solution stevie wrote)

This function is specific to the example but not generic for any amount paid
out.

Steve



Sept 9, 2009
"Steve" wrote in message
you can then return all the characters in front of it with the Left()
fumction. Would look like:
Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

Steve

No, it would not look like

Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

First of all, the constants are vbCr and vbLf: no quotes around them. With
the quotes, you're looking for the literal strings.

Second, you can't Or together character constants like that. Even if you
could, Or'ing them together in the InStr function like that makes no sense
at all.



Sept 22,2009
Sorry Steve, even I can see that this is a useless answer. I made it pretty
clear that "CW259" is just ONE possible value for the control.

"Steve" wrote:

Hello David,

Open your report in design view and select txtOrderID. Open properties and
go to the Data tab. Put the following expression in the Control Source
property:

=IIF([chkActive],"CW259","(CW259)")

Steve


John... Visio MVP

"Steve" wrote in message
...
Hello Billiam,



Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... very soon we will
'celebrate' 10.000
pageloads...)


For those who don't 'agree' with this mail , because $teve was 'helpfull'
with his post...
We warned him a thousand times... Sad, but he is not willing to stop
advertising...

He is just toying with these groups... advertising like hell... on and on...
for years...
oh yes... and sometimes he answers questions... indeed...
and sometimes good souls here give him credit for that...

== We are totally 'finished' with $teve now...
== Killfile 'StopThisAdvertising' and you won't see these mails....

Hi Douglas,

Thank you for responding!

The Holiday calendar is awesome...thank you for passing it on to me.

How do I add new Holidays, for example, like Family Day, in Canada (observed
in my Province of Ontario)...

Is there a way to take the values calculated and have them added to the
holiday table for reference by another function? I suspect a command button
on click function to append the results to the table? I have never done this
before...any advice? Or perhaps you have a better suggestion?

Thank you again for your help!

Billiam

"Douglas J. Steele" wrote:

Check my June, 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Billiam" wrote in message
news
Thank you for responding, Klatuu!

So all the dates would be in the same Holiday table?

Do you know if anyone has written a routine to autopopulate Holidays of a
fixed day? I suppose I could just enter the list in excel of those
Holidays
whoose day does not change, just the year, and autofil them for a decade
or
so and then imoprt them to the Holiday table...does this seem reasonable?

Thanks again for your help! It was fortunate that you mentioned the
Electric
Company scenario, as I had forgotten there are 2 occassions when we have
to
work Saturday, which is not a normal work day...

Thank you again for your continued help! I would have thrown Access out
without this forum, and very kind and knowledgable volunteers.
Thanks for shining a light for those of us stumbling around in the dark!
For
me, Access is like a bad addiction, you know it's bad for your health (for
me
frustration and stress) but the results are SO WORTH IT, if you can design
what you need...

Best Regards,

Billiam

"Klatuu" wrote:

Entering each holiday once a year is the normal way of doing it; however,
if
your calculations have to reach beyond the end of the current year, then
you
would need to load them ahead of time.

Certainly it is a pain to do so, so you might try writing a routine that
will autopopulate those that never change days or the day can be
calculated
and enter the rest by hand.

But normally, I would not load holidays that would not be used at the
local
level. But then, if your application needs to consider the foreign
holidays,
you would need them.

--
Dave Hargis, Microsoft Access MVP


"Billiam" wrote:

Hi John,

Thank you for responding!

The WorkDay field is also handy if the particular holiday is not
celebrated
in your particular area. For example, in Canada, certain holidays, such
as
Family Day, or St. Jean-Baptiste Day, are only celebrated in certain
Provinces, or, for that matter, take Thanksgiving which is celebrated
at a
different date in Canada and the USA...I think it gives a bit more
flexibility to the design having this field...but, what do I know!

What is confusing to me is how this holiday table is updated each year,
John...do I have 10 entries for New Year's Day for the next 10 years in
the
same Holiday table, or do I have a 2010 holiday table, a 2011 holiday
table?

This is probably nonsense since I am very unskilled in Access, but
trying to
become less unskilled,LOL, but I have this sense that I would have a
holiday
table with holiday name that I would link with a query possibly to the
t_DecadeDates...

Maybe you could explain the most efficient way to produce a list of
holidays
each year...this may be my bind spot, as I only see entering them one
by one
each year...this seems silly for days that never change, like New
Year's Day
and Christmas, however, Easter, and holidays which are calculated, it
would
seem there should be some query/programming to be able to produce that
list
without too much trouble? But then again, it's not like I am just
stumbling
over them on the internet, which makes me think it's not so easy
afterall?

As always, I would be aprreciative for your guidance on this!
Best Regards,

Billiam



I have produced a table of dates for the next 10 years (an imported
table
from excel).

"John W. Vinson" wrote:

On Sat, 3 Oct 2009 12:57:01 -0700, Billiam

wrote:

I am working on a making a timesheet db in Access 2007 for our
non-profit
organization. Advice has been given many times, and many ways on
calculating
the difference between two dates, and, excluding holidays.

I started with a holiday table like that proposed in the Access
wizard may
2007.
holiday key, PK autonumber
Holiday text description
HolidayDate, Date/Time
WorkDay, Yes/No

Is this really the best way to design a holiday table? it seems like
i am
breaking normalization rules by adding a different version date of
Easter in
the same table based on the year?

Any advice on the best design for a holiday table would be
appreciated!

Thanks,

Billiam

It looks good to me, and I don't see why that would be in the least
denormalized. Easter 2008 is a different day than Easter 2009.

My only quibble would be the WorkDay field; it seems overly
elaborate, since
you could very reasonably consider that the existance of a record for
a given
date would mark it as a holiday just by it being in the table. I
guess this
could be useful if you wanted to wish users a "Happy Groundhog Day"
even if
that day wasn't observed as a day off.
--

John W. Vinson [MVP]

Hi Billiam

FWIW, this is what *I* do...

Holidays are either:
1. On a set day (e.g. Christmas Day)
2. On the closest given day of the week to the set date
(e.g. closest Monday to 25 Feb)
3. On a day relative to Easter (e.g. Good Friday, Easter Monday)
4. On the nth (or nth-to-last) day-of-week of the month
(e.g. second Monday in June, last Thursday in November)

Here is a function that I use to calculate holiday dates given those
requirements:

=============== start code =======================
Public Function CalcHoliday(y As Integer, _
m As Integer, d As Integer, nType As Integer, _
Optional closest As Integer) As Date
Select Case nType
Case 0
' holiday is on the actual day of the year
' e.g. Christmas Day: CalcHoliday( y, 12, 25, 0 )
CalcHoliday = DateSerial(y, m, d)
Case 6, 7, 8
' holiday is on the weekday (c) closest to (before
' or after) the actual day of the year
' n=6:closest before, 7:closest after, 8:either way
' e.g. closest Monday to 4 July : CalcHoliday( y, 7, 4, 8, 2 )
CalcHoliday = ClosestWeekdayTo( _
DateSerial(y, m, d), closest, Choose(nType - 5, -1, 1, 0))
Case 9
' holiday is d days before/after Easter Sunday (m is ignored)
' e.g. Good Friday: CalcHoliday( y, 0, -2, 9 )
CalcHoliday = EasterDate(y) + d
Case Else
' holiday is on the nth WeekDay (d) of the month (m)
' (1=Sunday, 2=Monday, etc)
' if n is negative, then the nth-to-last
' e.g. Last Monday of October: CalcHoliday( y, 10, 2, -1 )
CalcHoliday = NthWeekdayInMonth(y, m, d, nType)
End Select
End Function
================= end code ======================

It will calculate the date of a holiday in any year, using a method that
depends upon the value of the third argument (nType)

If nType is:
0 : holiday is on the given date (y/m/d)
6, 7, 8 : holiday is on the closest weekday (closest) to the given date
(6 :closest before, 7:closest after, 8:closest either way
9 : holiday is d days relative to Easter
+(1..4) : holiday is nth weekday (d) in month
-(1..4) : holiday is nth-to-last weekday (d) in month


It uses three other functions (see further down below).

OK, now as well as having a Holidays table, as you describe, I have a table
named PerennialHolidays with these fields:
HolName: text, PK (e.g. "Christmas Day")
HolMonth: integer
HolDay: integer
HolType: a number indicating the type of calculation (see nType above)
HolClosestDay: optional integer
HolOrder: numeric (just to keep them in order for maintenance)

Records might look like this:

Christmas Day, 12, 25, 0
Easter Monday, 0, 1, 9 (one day after Easter Sunday)
Thanksgiving Day, 11, 5, 4 (4th Thurs in Nov)
Groundhog Day: 5, 2, 8, 3 (closest Tuesday to 2 May)
etc.

Now, to add all the holidays for a given year, one needs only run an append
query, like this:

Insert into Holidays (HolidayName, HolidayDate)
select HolName,
CalcHoliday( [Year], HolMonth, HolDay, HolType, Nz(HolClosestDay, 0) )
from PerennialHolidays
where HolName not in
(Select HolidayName from Holidays where Year(HolidayDate)=[Year]);

.... where [Year] is the year for which you are adding the records.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Below is the code for the three support functions:
NthWeekdayInMonth
ClosestWeekdayTo
EasterDate

(I take no credit for EasterDate!)

=============== start code =======================
Public Function NthWeekdayInMonth _
(y As Integer, m As Integer, _
wd As Integer, n As Integer) _
As Date
' Find the date of the nth WeekDay (wd) of the month (m)
' in given year (y) [d=1:Sunday, 2:Monday, etc]
' if n is negative, then the nth-to-last
Dim dt As Date
If n 0 Then
dt = DateSerial(y, m, 1)
dt = dt + (wd - Weekday(dt) + 7) Mod 7
dt = dt + (n - 1) * 7
Else
dt = DateSerial(y, m + 1, 0)
dt = dt - (Weekday(dt) - wd + 7) Mod 7
dt = dt + (n + 1) * 7
End If
NthWeekdayInMonth = dt
End Function

Public Function ClosestWeekdayTo _
(ByVal dt As Date, wd As Integer, _
Optional ByVal direction As Integer) _
As Date
' Find the date of the WeekDay (wd) closest to
' the given date (e.g. closest Monday to 1 March 1996)
' If dt falls on the given wd, return dt
' If direction is -ve, return first wd BEFORE the date
' If direction is +ve, return first wd AFTER the date
' If direction=0 (default) return CLOSEST wd
If Weekday(dt) wd Then
If direction = 0 Then
If (Weekday(dt) - wd + 10) Mod 7 3 Then
direction = -1
Else
direction = 1
End If
End If
If direction 0 Then
dt = dt - (Weekday(dt) - wd + 7) Mod 7
Else
dt = dt + (wd - Weekday(dt) + 7) Mod 7
End If
End If
ClosestWeekdayTo = dt
End Function

Public Function EasterDate(y As Integer) As Date
'
' CALCULATE EASTER SUNDAY DATE
'
' Adapted from an algorithm by Ronald W. Mallen
' http://www.assa.org.au/edm.html
'
' y is a 4 digit year 1583 to 4099
'
' Easter Sunday is the Sunday following the
' Paschal Full Moon (PFM) date for the year

' This algorithm is an arithmetic interpretation of the 3 step
' Easter Dating Method developed by Ron Mallen 1985, as a vast
' improvement on the method described in the Common Prayer Book
'
' Because this algorithm is a direct translation of the
' official tables, it can be easily proved to be 100% correct
'
' This algorithm derives values by sequential inter-dependent
' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS!
'
' All variables are integer data types
'
' It's free! Please do not modify code or comments!

Dim d As Integer, m As Integer
Dim Century As Integer, Remain19 As Integer, _
temp As Integer 'intermediate results
Dim tA As Integer, tB As Integer, tC As Integer, _
tD As Integer, tE As Integer 'table A to E results

Century = y \ 100 'first 2 digits of year
Remain19 = y Mod 19 'remainder of year / 19

'calculate PFM date
temp = (Century - 15) \ 2 + 202 - 11 * Remain19
If Century 26 Then temp = temp - 1
If Century 38 Then temp = temp - 1

If ((Century = 21) Or (Century = 24) Or (Century = 25) _
Or (Century = 33) Or (Century = 36) Or (Century = 37)) _
Then temp = temp - 1

temp = temp Mod 30

tA = temp + 21
If temp = 29 Then tA = tA - 1
If (temp = 28 And Remain19 10) Then tA = tA - 1

'find the next Sunday
tB = (tA - 19) Mod 7

tC = (40 - Century) Mod 4
If tC = 3 Then tC = tC + 1
If tC 1 Then tC = tC + 1

temp = y Mod 100
tD = (temp + temp \ 4) Mod 7

tE = ((20 - tB - tC - tD) Mod 7) + 1
d = tA + tE

'return the date
If d 31 Then
d = d - 31
m = 4
Else
m = 3
End If

EasterDate = DateSerial(y, m, d)

End Function
================= end code ======================



"Billiam" wrote in message
...
I am working on a making a timesheet db in Access 2007 for our non-profit
organization. Advice has been given many times, and many ways on
calculating
the difference between two dates, and, excluding holidays.

I started with a holiday table like that proposed in the Access wizard may
2007.
holiday key, PK autonumber
Holiday text description
HolidayDate, Date/Time
WorkDay, Yes/No

Is this really the best way to design a holiday table? it seems like i am
breaking normalization rules by adding a different version date of Easter
in
the same table based on the year?

Any advice on the best design for a holiday table would be appreciated!

Thanks,

Billiam

PS: ...and it didn't cost you a cent!

Mind you, I have posted this code elsewhere, so knowing Steve it will be at
least part of the "solution" that he sells you "for a modest fee" g

--
Graham

"Graham Mandeno" wrote in message
...
Hi Billiam

FWIW, this is what *I* do...

Holidays are either:
1. On a set day (e.g. Christmas Day)
2. On the closest given day of the week to the set date
(e.g. closest Monday to 25 Feb)
3. On a day relative to Easter (e.g. Good Friday, Easter Monday)
4. On the nth (or nth-to-last) day-of-week of the month
(e.g. second Monday in June, last Thursday in November)

Here is a function that I use to calculate holiday dates given those
requirements:

=============== start code =======================
Public Function CalcHoliday(y As Integer, _
m As Integer, d As Integer, nType As Integer, _
Optional closest As Integer) As Date
Select Case nType
Case 0
' holiday is on the actual day of the year
' e.g. Christmas Day: CalcHoliday( y, 12, 25, 0 )
CalcHoliday = DateSerial(y, m, d)
Case 6, 7, 8
' holiday is on the weekday (c) closest to (before
' or after) the actual day of the year
' n=6:closest before, 7:closest after, 8:either way
' e.g. closest Monday to 4 July : CalcHoliday( y, 7, 4, 8, 2 )
CalcHoliday = ClosestWeekdayTo( _
DateSerial(y, m, d), closest, Choose(nType - 5, -1, 1, 0))
Case 9
' holiday is d days before/after Easter Sunday (m is ignored)
' e.g. Good Friday: CalcHoliday( y, 0, -2, 9 )
CalcHoliday = EasterDate(y) + d
Case Else
' holiday is on the nth WeekDay (d) of the month (m)
' (1=Sunday, 2=Monday, etc)
' if n is negative, then the nth-to-last
' e.g. Last Monday of October: CalcHoliday( y, 10, 2, -1 )
CalcHoliday = NthWeekdayInMonth(y, m, d, nType)
End Select
End Function
================= end code ======================

It will calculate the date of a holiday in any year, using a method that
depends upon the value of the third argument (nType)

If nType is:
0 : holiday is on the given date (y/m/d)
6, 7, 8 : holiday is on the closest weekday (closest) to the given date
(6 :closest before, 7:closest after, 8:closest either way
9 : holiday is d days relative to Easter
+(1..4) : holiday is nth weekday (d) in month
-(1..4) : holiday is nth-to-last weekday (d) in month


It uses three other functions (see further down below).

OK, now as well as having a Holidays table, as you describe, I have a
table named PerennialHolidays with these fields:
HolName: text, PK (e.g. "Christmas Day")
HolMonth: integer
HolDay: integer
HolType: a number indicating the type of calculation (see nType above)
HolClosestDay: optional integer
HolOrder: numeric (just to keep them in order for maintenance)

Records might look like this:

Christmas Day, 12, 25, 0
Easter Monday, 0, 1, 9 (one day after Easter Sunday)
Thanksgiving Day, 11, 5, 4 (4th Thurs in Nov)
Groundhog Day: 5, 2, 8, 3 (closest Tuesday to 2 May)
etc.

Now, to add all the holidays for a given year, one needs only run an
append query, like this:

Insert into Holidays (HolidayName, HolidayDate)
select HolName,
CalcHoliday( [Year], HolMonth, HolDay, HolType, Nz(HolClosestDay, 0) )
from PerennialHolidays
where HolName not in
(Select HolidayName from Holidays where Year(HolidayDate)=[Year]);

... where [Year] is the year for which you are adding the records.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Below is the code for the three support functions:
NthWeekdayInMonth
ClosestWeekdayTo
EasterDate

(I take no credit for EasterDate!)

=============== start code =======================
Public Function NthWeekdayInMonth _
(y As Integer, m As Integer, _
wd As Integer, n As Integer) _
As Date
' Find the date of the nth WeekDay (wd) of the month (m)
' in given year (y) [d=1:Sunday, 2:Monday, etc]
' if n is negative, then the nth-to-last
Dim dt As Date
If n 0 Then
dt = DateSerial(y, m, 1)
dt = dt + (wd - Weekday(dt) + 7) Mod 7
dt = dt + (n - 1) * 7
Else
dt = DateSerial(y, m + 1, 0)
dt = dt - (Weekday(dt) - wd + 7) Mod 7
dt = dt + (n + 1) * 7
End If
NthWeekdayInMonth = dt
End Function

Public Function ClosestWeekdayTo _
(ByVal dt As Date, wd As Integer, _
Optional ByVal direction As Integer) _
As Date
' Find the date of the WeekDay (wd) closest to
' the given date (e.g. closest Monday to 1 March 1996)
' If dt falls on the given wd, return dt
' If direction is -ve, return first wd BEFORE the date
' If direction is +ve, return first wd AFTER the date
' If direction=0 (default) return CLOSEST wd
If Weekday(dt) wd Then
If direction = 0 Then
If (Weekday(dt) - wd + 10) Mod 7 3 Then
direction = -1
Else
direction = 1
End If
End If
If direction 0 Then
dt = dt - (Weekday(dt) - wd + 7) Mod 7
Else
dt = dt + (wd - Weekday(dt) + 7) Mod 7
End If
End If
ClosestWeekdayTo = dt
End Function

Public Function EasterDate(y As Integer) As Date
'
' CALCULATE EASTER SUNDAY DATE
'
' Adapted from an algorithm by Ronald W. Mallen
' http://www.assa.org.au/edm.html
'
' y is a 4 digit year 1583 to 4099
'
' Easter Sunday is the Sunday following the
' Paschal Full Moon (PFM) date for the year

' This algorithm is an arithmetic interpretation of the 3 step
' Easter Dating Method developed by Ron Mallen 1985, as a vast
' improvement on the method described in the Common Prayer Book
'
' Because this algorithm is a direct translation of the
' official tables, it can be easily proved to be 100% correct
'
' This algorithm derives values by sequential inter-dependent
' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS!
'
' All variables are integer data types
'
' It's free! Please do not modify code or comments!

Dim d As Integer, m As Integer
Dim Century As Integer, Remain19 As Integer, _
temp As Integer 'intermediate results
Dim tA As Integer, tB As Integer, tC As Integer, _
tD As Integer, tE As Integer 'table A to E results

Century = y \ 100 'first 2 digits of year
Remain19 = y Mod 19 'remainder of year / 19

'calculate PFM date
temp = (Century - 15) \ 2 + 202 - 11 * Remain19
If Century 26 Then temp = temp - 1
If Century 38 Then temp = temp - 1

If ((Century = 21) Or (Century = 24) Or (Century = 25) _
Or (Century = 33) Or (Century = 36) Or (Century = 37)) _
Then temp = temp - 1

temp = temp Mod 30

tA = temp + 21
If temp = 29 Then tA = tA - 1
If (temp = 28 And Remain19 10) Then tA = tA - 1

'find the next Sunday
tB = (tA - 19) Mod 7

tC = (40 - Century) Mod 4
If tC = 3 Then tC = tC + 1
If tC 1 Then tC = tC + 1

temp = y Mod 100
tD = (temp + temp \ 4) Mod 7

tE = ((20 - tB - tC - tD) Mod 7) + 1
d = tA + tE

'return the date
If d 31 Then
d = d - 31
m = 4
Else
m = 3
End If

EasterDate = DateSerial(y, m, d)

End Function
================= end code ======================



"Billiam" wrote in message
...
I am working on a making a timesheet db in Access 2007 for our non-profit
organization. Advice has been given many times, and many ways on
calculating
the difference between two dates, and, excluding holidays.

I started with a holiday table like that proposed in the Access wizard
may
2007.
holiday key, PK autonumber
Holiday text description
HolidayDate, Date/Time
WorkDay, Yes/No

Is this really the best way to design a holiday table? it seems like i am
breaking normalization rules by adding a different version date of
Easter in
the same table based on the year?

Any advice on the best design for a holiday table would be appreciated!

Thanks,

Billiam

Thank you Graham, and with great advice like yours, that is priceless!

Thank you very very VERY much!

Billiam

"Graham Mandeno" wrote:

PS: ...and it didn't cost you a cent!

Mind you, I have posted this code elsewhere, so knowing Steve it will be at
least part of the "solution" that he sells you "for a modest fee" g

--
Graham

"Graham Mandeno" wrote in message
...
Hi Billiam

FWIW, this is what *I* do...

Holidays are either:
1. On a set day (e.g. Christmas Day)
2. On the closest given day of the week to the set date
(e.g. closest Monday to 25 Feb)
3. On a day relative to Easter (e.g. Good Friday, Easter Monday)
4. On the nth (or nth-to-last) day-of-week of the month
(e.g. second Monday in June, last Thursday in November)

Here is a function that I use to calculate holiday dates given those
requirements:

=============== start code =======================
Public Function CalcHoliday(y As Integer, _
m As Integer, d As Integer, nType As Integer, _
Optional closest As Integer) As Date
Select Case nType
Case 0
' holiday is on the actual day of the year
' e.g. Christmas Day: CalcHoliday( y, 12, 25, 0 )
CalcHoliday = DateSerial(y, m, d)
Case 6, 7, 8
' holiday is on the weekday (c) closest to (before
' or after) the actual day of the year
' n=6:closest before, 7:closest after, 8:either way
' e.g. closest Monday to 4 July : CalcHoliday( y, 7, 4, 8, 2 )
CalcHoliday = ClosestWeekdayTo( _
DateSerial(y, m, d), closest, Choose(nType - 5, -1, 1, 0))
Case 9
' holiday is d days before/after Easter Sunday (m is ignored)
' e.g. Good Friday: CalcHoliday( y, 0, -2, 9 )
CalcHoliday = EasterDate(y) + d
Case Else
' holiday is on the nth WeekDay (d) of the month (m)
' (1=Sunday, 2=Monday, etc)
' if n is negative, then the nth-to-last
' e.g. Last Monday of October: CalcHoliday( y, 10, 2, -1 )
CalcHoliday = NthWeekdayInMonth(y, m, d, nType)
End Select
End Function
================= end code ======================

It will calculate the date of a holiday in any year, using a method that
depends upon the value of the third argument (nType)

If nType is:
0 : holiday is on the given date (y/m/d)
6, 7, 8 : holiday is on the closest weekday (closest) to the given date
(6 :closest before, 7:closest after, 8:closest either way
9 : holiday is d days relative to Easter
+(1..4) : holiday is nth weekday (d) in month
-(1..4) : holiday is nth-to-last weekday (d) in month


It uses three other functions (see further down below).

OK, now as well as having a Holidays table, as you describe, I have a
table named PerennialHolidays with these fields:
HolName: text, PK (e.g. "Christmas Day")
HolMonth: integer
HolDay: integer
HolType: a number indicating the type of calculation (see nType above)
HolClosestDay: optional integer
HolOrder: numeric (just to keep them in order for maintenance)

Records might look like this:

Christmas Day, 12, 25, 0
Easter Monday, 0, 1, 9 (one day after Easter Sunday)
Thanksgiving Day, 11, 5, 4 (4th Thurs in Nov)
Groundhog Day: 5, 2, 8, 3 (closest Tuesday to 2 May)
etc.

Now, to add all the holidays for a given year, one needs only run an
append query, like this:

Insert into Holidays (HolidayName, HolidayDate)
select HolName,
CalcHoliday( [Year], HolMonth, HolDay, HolType, Nz(HolClosestDay, 0) )
from PerennialHolidays
where HolName not in
(Select HolidayName from Holidays where Year(HolidayDate)=[Year]);

... where [Year] is the year for which you are adding the records.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Below is the code for the three support functions:
NthWeekdayInMonth
ClosestWeekdayTo
EasterDate

(I take no credit for EasterDate!)

=============== start code =======================
Public Function NthWeekdayInMonth _
(y As Integer, m As Integer, _
wd As Integer, n As Integer) _
As Date
' Find the date of the nth WeekDay (wd) of the month (m)
' in given year (y) [d=1:Sunday, 2:Monday, etc]
' if n is negative, then the nth-to-last
Dim dt As Date
If n 0 Then
dt = DateSerial(y, m, 1)
dt = dt + (wd - Weekday(dt) + 7) Mod 7
dt = dt + (n - 1) * 7
Else
dt = DateSerial(y, m + 1, 0)
dt = dt - (Weekday(dt) - wd + 7) Mod 7
dt = dt + (n + 1) * 7
End If
NthWeekdayInMonth = dt
End Function

Public Function ClosestWeekdayTo _
(ByVal dt As Date, wd As Integer, _
Optional ByVal direction As Integer) _
As Date
' Find the date of the WeekDay (wd) closest to
' the given date (e.g. closest Monday to 1 March 1996)
' If dt falls on the given wd, return dt
' If direction is -ve, return first wd BEFORE the date
' If direction is +ve, return first wd AFTER the date
' If direction=0 (default) return CLOSEST wd
If Weekday(dt) wd Then
If direction = 0 Then
If (Weekday(dt) - wd + 10) Mod 7 3 Then
direction = -1
Else
direction = 1
End If
End If
If direction 0 Then
dt = dt - (Weekday(dt) - wd + 7) Mod 7
Else
dt = dt + (wd - Weekday(dt) + 7) Mod 7
End If
End If
ClosestWeekdayTo = dt
End Function

Public Function EasterDate(y As Integer) As Date
'
' CALCULATE EASTER SUNDAY DATE
'
' Adapted from an algorithm by Ronald W. Mallen
' http://www.assa.org.au/edm.html
'
' y is a 4 digit year 1583 to 4099
'
' Easter Sunday is the Sunday following the
' Paschal Full Moon (PFM) date for the year

' This algorithm is an arithmetic interpretation of the 3 step
' Easter Dating Method developed by Ron Mallen 1985, as a vast
' improvement on the method described in the Common Prayer Book
'
' Because this algorithm is a direct translation of the
' official tables, it can be easily proved to be 100% correct
'
' This algorithm derives values by sequential inter-dependent
' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS!
'
' All variables are integer data types
'
' It's free! Please do not modify code or comments!

Dim d As Integer, m As Integer
Dim Century As Integer, Remain19 As Integer, _
temp As Integer 'intermediate results
Dim tA As Integer, tB As Integer, tC As Integer, _
tD As Integer, tE As Integer 'table A to E results

Century = y \ 100 'first 2 digits of year
Remain19 = y Mod 19 'remainder of year / 19

'calculate PFM date
temp = (Century - 15) \ 2 + 202 - 11 * Remain19
If Century 26 Then temp = temp - 1
If Century 38 Then temp = temp - 1

If ((Century = 21) Or (Century = 24) Or (Century = 25) _
Or (Century = 33) Or (Century = 36) Or (Century = 37)) _
Then temp = temp - 1

temp = temp Mod 30

tA = temp + 21
If temp = 29 Then tA = tA - 1
If (temp = 28 And Remain19 10) Then tA = tA - 1

'find the next Sunday
tB = (tA - 19) Mod 7

tC = (40 - Century) Mod 4
If tC = 3 Then tC = tC + 1
If tC 1 Then tC = tC + 1

temp = y Mod 100
tD = (temp + temp \ 4) Mod 7

tE = ((20 - tB - tC - tD) Mod 7) + 1
d = tA + tE

'return the date
If d 31 Then
d = d - 31
m = 4
Else
m = 3
End If

EasterDate = DateSerial(y, m, d)

End Function
================= end code ======================



"Billiam" wrote in message
...
I am working on a making a timesheet db in Access 2007 for our non-profit
organization. Advice has been given many times, and many ways on
calculating
the difference between two dates, and, excluding holidays.

I started with a holiday table like that proposed in the Access wizard
may
2007.
holiday key, PK autonumber
Holiday text description
HolidayDate, Date/Time
WorkDay, Yes/No

Is this really the best way to design a holiday table? it seems like i am
breaking normalization rules by adding a different version date of
Easter in
the same table based on the year?

Any advice on the best design for a holiday table would be appreciated!

Thanks,

Billiam

I will give this a try, Graham! Thanks for the detailed post! If I understand
you correctly, this would actually calculate Easter say the year 2019?

Billiam

"Graham Mandeno" wrote:

Hi Billiam

FWIW, this is what *I* do...

Holidays are either:
1. On a set day (e.g. Christmas Day)
2. On the closest given day of the week to the set date
(e.g. closest Monday to 25 Feb)
3. On a day relative to Easter (e.g. Good Friday, Easter Monday)
4. On the nth (or nth-to-last) day-of-week of the month
(e.g. second Monday in June, last Thursday in November)

Here is a function that I use to calculate holiday dates given those
requirements:

=============== start code =======================
Public Function CalcHoliday(y As Integer, _
m As Integer, d As Integer, nType As Integer, _
Optional closest As Integer) As Date
Select Case nType
Case 0
' holiday is on the actual day of the year
' e.g. Christmas Day: CalcHoliday( y, 12, 25, 0 )
CalcHoliday = DateSerial(y, m, d)
Case 6, 7, 8
' holiday is on the weekday (c) closest to (before
' or after) the actual day of the year
' n=6:closest before, 7:closest after, 8:either way
' e.g. closest Monday to 4 July : CalcHoliday( y, 7, 4, 8, 2 )
CalcHoliday = ClosestWeekdayTo( _
DateSerial(y, m, d), closest, Choose(nType - 5, -1, 1, 0))
Case 9
' holiday is d days before/after Easter Sunday (m is ignored)
' e.g. Good Friday: CalcHoliday( y, 0, -2, 9 )
CalcHoliday = EasterDate(y) + d
Case Else
' holiday is on the nth WeekDay (d) of the month (m)
' (1=Sunday, 2=Monday, etc)
' if n is negative, then the nth-to-last
' e.g. Last Monday of October: CalcHoliday( y, 10, 2, -1 )
CalcHoliday = NthWeekdayInMonth(y, m, d, nType)
End Select
End Function
================= end code ======================

It will calculate the date of a holiday in any year, using a method that
depends upon the value of the third argument (nType)

If nType is:
0 : holiday is on the given date (y/m/d)
6, 7, 8 : holiday is on the closest weekday (closest) to the given date
(6 :closest before, 7:closest after, 8:closest either way
9 : holiday is d days relative to Easter
+(1..4) : holiday is nth weekday (d) in month
-(1..4) : holiday is nth-to-last weekday (d) in month


It uses three other functions (see further down below).

OK, now as well as having a Holidays table, as you describe, I have a table
named PerennialHolidays with these fields:
HolName: text, PK (e.g. "Christmas Day")
HolMonth: integer
HolDay: integer
HolType: a number indicating the type of calculation (see nType above)
HolClosestDay: optional integer
HolOrder: numeric (just to keep them in order for maintenance)

Records might look like this:

Christmas Day, 12, 25, 0
Easter Monday, 0, 1, 9 (one day after Easter Sunday)
Thanksgiving Day, 11, 5, 4 (4th Thurs in Nov)
Groundhog Day: 5, 2, 8, 3 (closest Tuesday to 2 May)
etc.

Now, to add all the holidays for a given year, one needs only run an append
query, like this:

Insert into Holidays (HolidayName, HolidayDate)
select HolName,
CalcHoliday( [Year], HolMonth, HolDay, HolType, Nz(HolClosestDay, 0) )
from PerennialHolidays
where HolName not in
(Select HolidayName from Holidays where Year(HolidayDate)=[Year]);

.... where [Year] is the year for which you are adding the records.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Below is the code for the three support functions:
NthWeekdayInMonth
ClosestWeekdayTo
EasterDate

(I take no credit for EasterDate!)

=============== start code =======================
Public Function NthWeekdayInMonth _
(y As Integer, m As Integer, _
wd As Integer, n As Integer) _
As Date
' Find the date of the nth WeekDay (wd) of the month (m)
' in given year (y) [d=1:Sunday, 2:Monday, etc]
' if n is negative, then the nth-to-last
Dim dt As Date
If n 0 Then
dt = DateSerial(y, m, 1)
dt = dt + (wd - Weekday(dt) + 7) Mod 7
dt = dt + (n - 1) * 7
Else
dt = DateSerial(y, m + 1, 0)
dt = dt - (Weekday(dt) - wd + 7) Mod 7
dt = dt + (n + 1) * 7
End If
NthWeekdayInMonth = dt
End Function

Public Function ClosestWeekdayTo _
(ByVal dt As Date, wd As Integer, _
Optional ByVal direction As Integer) _
As Date
' Find the date of the WeekDay (wd) closest to
' the given date (e.g. closest Monday to 1 March 1996)
' If dt falls on the given wd, return dt
' If direction is -ve, return first wd BEFORE the date
' If direction is +ve, return first wd AFTER the date
' If direction=0 (default) return CLOSEST wd
If Weekday(dt) wd Then
If direction = 0 Then
If (Weekday(dt) - wd + 10) Mod 7 3 Then
direction = -1
Else
direction = 1
End If
End If
If direction 0 Then
dt = dt - (Weekday(dt) - wd + 7) Mod 7
Else
dt = dt + (wd - Weekday(dt) + 7) Mod 7
End If
End If
ClosestWeekdayTo = dt
End Function

Public Function EasterDate(y As Integer) As Date
'
' CALCULATE EASTER SUNDAY DATE
'
' Adapted from an algorithm by Ronald W. Mallen
' http://www.assa.org.au/edm.html
'
' y is a 4 digit year 1583 to 4099
'
' Easter Sunday is the Sunday following the
' Paschal Full Moon (PFM) date for the year

' This algorithm is an arithmetic interpretation of the 3 step
' Easter Dating Method developed by Ron Mallen 1985, as a vast
' improvement on the method described in the Common Prayer Book
'
' Because this algorithm is a direct translation of the
' official tables, it can be easily proved to be 100% correct
'
' This algorithm derives values by sequential inter-dependent
' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS!
'
' All variables are integer data types
'
' It's free! Please do not modify code or comments!

Dim d As Integer, m As Integer
Dim Century As Integer, Remain19 As Integer, _
temp As Integer 'intermediate results
Dim tA As Integer, tB As Integer, tC As Integer, _
tD As Integer, tE As Integer 'table A to E results

Century = y \ 100 'first 2 digits of year
Remain19 = y Mod 19 'remainder of year / 19

'calculate PFM date
temp = (Century - 15) \ 2 + 202 - 11 * Remain19
If Century 26 Then temp = temp - 1
If Century 38 Then temp = temp - 1

If ((Century = 21) Or (Century = 24) Or (Century = 25) _
Or (Century = 33) Or (Century = 36) Or (Century = 37)) _
Then temp = temp - 1

temp = temp Mod 30

tA = temp + 21
If temp = 29 Then tA = tA - 1
If (temp = 28 And Remain19 10) Then tA = tA - 1

'find the next Sunday
tB = (tA - 19) Mod 7

tC = (40 - Century) Mod 4
If tC = 3 Then tC = tC + 1
If tC 1 Then tC = tC + 1

temp = y Mod 100
tD = (temp + temp \ 4) Mod 7

tE = ((20 - tB - tC - tD) Mod 7) + 1
d = tA + tE

'return the date
If d 31 Then
d = d - 31
m = 4
Else
m = 3
End If

EasterDate = DateSerial(y, m, d)

End Function
================= end code ======================



"Billiam" wrote in message
...
I am working on a making a timesheet db in Access 2007 for our non-profit
organization. Advice has been given many times, and many ways on
calculating
the difference between two dates, and, excluding holidays.

I started with a holiday table like that proposed in the Access wizard may
2007.
holiday key, PK autonumber
Holiday text description
HolidayDate, Date/Time
WorkDay, Yes/No

Is this really the best way to design a holiday table? it seems like i am
breaking normalization rules by adding a different version date of Easter
in
the same table based on the year?

Any advice on the best design for a holiday table would be appreciated!

Thanks,

Billiam

Hi Billiam

You're welcome!

this would actually calculate Easter say the year 2019?

Sure would! Any year from 1583 to 4099, so your database will long out-live
you ;-)

--
Graham

"Billiam" wrote in message
...
I will give this a try, Graham! Thanks for the detailed post! If I
understand
you correctly, this would actually calculate Easter say the year 2019?

Billiam

"Graham Mandeno" wrote:

Hi Billiam

FWIW, this is what *I* do...

Holidays are either:
1. On a set day (e.g. Christmas Day)
2. On the closest given day of the week to the set date
(e.g. closest Monday to 25 Feb)
3. On a day relative to Easter (e.g. Good Friday, Easter Monday)
4. On the nth (or nth-to-last) day-of-week of the month
(e.g. second Monday in June, last Thursday in November)

Here is a function that I use to calculate holiday dates given those
requirements:

=============== start code =======================
Public Function CalcHoliday(y As Integer, _
m As Integer, d As Integer, nType As Integer, _
Optional closest As Integer) As Date
Select Case nType
Case 0
' holiday is on the actual day of the year
' e.g. Christmas Day: CalcHoliday( y, 12, 25, 0 )
CalcHoliday = DateSerial(y, m, d)
Case 6, 7, 8
' holiday is on the weekday (c) closest to (before
' or after) the actual day of the year
' n=6:closest before, 7:closest after, 8:either way
' e.g. closest Monday to 4 July : CalcHoliday( y, 7, 4, 8, 2 )
CalcHoliday = ClosestWeekdayTo( _
DateSerial(y, m, d), closest, Choose(nType - 5, -1, 1, 0))
Case 9
' holiday is d days before/after Easter Sunday (m is ignored)
' e.g. Good Friday: CalcHoliday( y, 0, -2, 9 )
CalcHoliday = EasterDate(y) + d
Case Else
' holiday is on the nth WeekDay (d) of the month (m)
' (1=Sunday, 2=Monday, etc)
' if n is negative, then the nth-to-last
' e.g. Last Monday of October: CalcHoliday( y, 10, 2, -1 )
CalcHoliday = NthWeekdayInMonth(y, m, d, nType)
End Select
End Function
================= end code ======================

It will calculate the date of a holiday in any year, using a method that
depends upon the value of the third argument (nType)

If nType is:
0 : holiday is on the given date (y/m/d)
6, 7, 8 : holiday is on the closest weekday (closest) to the given
date
(6 :closest before, 7:closest after, 8:closest either way
9 : holiday is d days relative to Easter
+(1..4) : holiday is nth weekday (d) in month
-(1..4) : holiday is nth-to-last weekday (d) in month


It uses three other functions (see further down below).

OK, now as well as having a Holidays table, as you describe, I have a
table
named PerennialHolidays with these fields:
HolName: text, PK (e.g. "Christmas Day")
HolMonth: integer
HolDay: integer
HolType: a number indicating the type of calculation (see nType
above)
HolClosestDay: optional integer
HolOrder: numeric (just to keep them in order for maintenance)

Records might look like this:

Christmas Day, 12, 25, 0
Easter Monday, 0, 1, 9 (one day after Easter Sunday)
Thanksgiving Day, 11, 5, 4 (4th Thurs in Nov)
Groundhog Day: 5, 2, 8, 3 (closest Tuesday to 2 May)
etc.

Now, to add all the holidays for a given year, one needs only run an
append
query, like this:

Insert into Holidays (HolidayName, HolidayDate)
select HolName,
CalcHoliday( [Year], HolMonth, HolDay, HolType, Nz(HolClosestDay, 0) )
from PerennialHolidays
where HolName not in
(Select HolidayName from Holidays where Year(HolidayDate)=[Year]);

.... where [Year] is the year for which you are adding the records.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Below is the code for the three support functions:
NthWeekdayInMonth
ClosestWeekdayTo
EasterDate

(I take no credit for EasterDate!)

=============== start code =======================
Public Function NthWeekdayInMonth _
(y As Integer, m As Integer, _
wd As Integer, n As Integer) _
As Date
' Find the date of the nth WeekDay (wd) of the month (m)
' in given year (y) [d=1:Sunday, 2:Monday, etc]
' if n is negative, then the nth-to-last
Dim dt As Date
If n 0 Then
dt = DateSerial(y, m, 1)
dt = dt + (wd - Weekday(dt) + 7) Mod 7
dt = dt + (n - 1) * 7
Else
dt = DateSerial(y, m + 1, 0)
dt = dt - (Weekday(dt) - wd + 7) Mod 7
dt = dt + (n + 1) * 7
End If
NthWeekdayInMonth = dt
End Function

Public Function ClosestWeekdayTo _
(ByVal dt As Date, wd As Integer, _
Optional ByVal direction As Integer) _
As Date
' Find the date of the WeekDay (wd) closest to
' the given date (e.g. closest Monday to 1 March 1996)
' If dt falls on the given wd, return dt
' If direction is -ve, return first wd BEFORE the date
' If direction is +ve, return first wd AFTER the date
' If direction=0 (default) return CLOSEST wd
If Weekday(dt) wd Then
If direction = 0 Then
If (Weekday(dt) - wd + 10) Mod 7 3 Then
direction = -1
Else
direction = 1
End If
End If
If direction 0 Then
dt = dt - (Weekday(dt) - wd + 7) Mod 7
Else
dt = dt + (wd - Weekday(dt) + 7) Mod 7
End If
End If
ClosestWeekdayTo = dt
End Function

Public Function EasterDate(y As Integer) As Date
'
' CALCULATE EASTER SUNDAY DATE
'
' Adapted from an algorithm by Ronald W. Mallen
' http://www.assa.org.au/edm.html
'
' y is a 4 digit year 1583 to 4099
'
' Easter Sunday is the Sunday following the
' Paschal Full Moon (PFM) date for the year

' This algorithm is an arithmetic interpretation of the 3 step
' Easter Dating Method developed by Ron Mallen 1985, as a vast
' improvement on the method described in the Common Prayer Book
'
' Because this algorithm is a direct translation of the
' official tables, it can be easily proved to be 100% correct
'
' This algorithm derives values by sequential inter-dependent
' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS!
'
' All variables are integer data types
'
' It's free! Please do not modify code or comments!

Dim d As Integer, m As Integer
Dim Century As Integer, Remain19 As Integer, _
temp As Integer 'intermediate results
Dim tA As Integer, tB As Integer, tC As Integer, _
tD As Integer, tE As Integer 'table A to E results

Century = y \ 100 'first 2 digits of year
Remain19 = y Mod 19 'remainder of year / 19

'calculate PFM date
temp = (Century - 15) \ 2 + 202 - 11 * Remain19
If Century 26 Then temp = temp - 1
If Century 38 Then temp = temp - 1

If ((Century = 21) Or (Century = 24) Or (Century = 25) _
Or (Century = 33) Or (Century = 36) Or (Century = 37)) _
Then temp = temp - 1

temp = temp Mod 30

tA = temp + 21
If temp = 29 Then tA = tA - 1
If (temp = 28 And Remain19 10) Then tA = tA - 1

'find the next Sunday
tB = (tA - 19) Mod 7

tC = (40 - Century) Mod 4
If tC = 3 Then tC = tC + 1
If tC 1 Then tC = tC + 1

temp = y Mod 100
tD = (temp + temp \ 4) Mod 7

tE = ((20 - tB - tC - tD) Mod 7) + 1
d = tA + tE

'return the date
If d 31 Then
d = d - 31
m = 4
Else
m = 3
End If

EasterDate = DateSerial(y, m, d)

End Function
================= end code ======================



"Billiam" wrote in message
...
I am working on a making a timesheet db in Access 2007 for our
non-profit
organization. Advice has been given many times, and many ways on
calculating
the difference between two dates, and, excluding holidays.

I started with a holiday table like that proposed in the Access wizard
may
2007.
holiday key, PK autonumber
Holiday text description
HolidayDate, Date/Time
WorkDay, Yes/No

Is this really the best way to design a holiday table? it seems like i
am
breaking normalization rules by adding a different version date of
Easter
in
the same table based on the year?

Any advice on the best design for a holiday table would be appreciated!

Thanks,

Billiam