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 said:

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