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
