Holiday Table Design

[Graham Mandeno]

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

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

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 ======================

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

 

[Douglas J. Steele]

I thought the article explained how the details for each holiday was stored
in the table.

Family Day falls on the third Monday in February. That means you'd set
FixedMonth to 2 (for February), FixedWeekday to 2 (for Monday) and
FixedWeekdayofMonth to 3 (for the third Monday)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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