In your September 2004 column (“Working All Day”), you talked about requiring a table of holidays in order to be able to include them in addition to weekends when calculating working days. Is there a way to automate creating such a table, so that I don’t need to repopulate it each year?
I was afraid someone was going to ask that…
Let me start by saying that, depending on which holidays you need to include, I don’t think it’s possible to completely automate the process. My understanding is that some holidays are based on the lunar calendar, which relies on the first sighting of the new moon, which can’t be accurately predicted. I’m going to concentrate on those holidays in the western world that are tied firmly to the calendar. Hopefully you’ll be able to extend the concepts to other areas!
The holidays with which I’m familiar fall into three categories:
- Those that occur on the same date each year: New Year’s Day is always January 1, Canada Day is always July 1, Independence Day in the U.S. is always July 4, and so on.
- Those that occur on the same relative day each year: the Spring Bank Holiday in the United Kingdom is always the fourth Monday in May, Victoria Day in Canada is always the second-to-last Monday in May, Labour Day in Canada (and Labor Day in the United States) is always the first Monday in September, and so on.
- Those that have rules for their calculation: Easter is always the first Sunday after the first full moon after the vernal equinox, and so on.
Obviously the first category of holidays (always on the same day each year) is trivial to handle: Assuming you know the month and day, the DateSerial function will give you the date for any year.
Relative dates aren’t much harder to calculate. Access provides a number of very nice date-related functions, so it’s fairly straightforward to be able to calculate a specific weekday in a month. For instance, the following function will return the date for a “generic” day of the month (such as the third Monday of the month):
Function GetDate (WhatYear As Long, _
WhatMonth As Long, _
WhatWeekDay As Long, _
WhatWeekDayOfMonth As Long) As Date
Dim dtmFirstOfMonth As Date
dtmFirstOfMonth = _
DateSerial(WhatYear, WhatMonth, 1)
GetDate = DateAdd("w", _
(WhatWeekDayOfMonth - 1) * 7, _
DateAdd("d", _
(WhatWeekDay - _
Weekday(dtmFirstOfMonth) + 7) Mod 7, _
dtmFirstOfMonth) _
)
End Function
The parameters to this are probably self-explanatory, but just in case it’s only obvious to me, the four parameters are:
- WhatYear is the year for the date.
- WhatMonth is the number of the month (January = 1, February = 2, and so on to December = 12).
- WhatWeekDay is the number of the weekday (Sunday = 1, Monday = 2, and so on to Saturday = 7; note that these are the same values as the VBA constants vbSunday, vbMonday, …, vbSaturday).
- WhatWeekdayOfMonth is the order number of the weekday for the month (1 = first occurrence of that weekday in the month, 2 = second occurrence of that weekday in the month, and so on).
For example, the fourth Thursday in June 2005 would be:
GetDate(2005, 6, vbThursday, 4)
Alternatively, this call would return 23 June 2005:
GetDate(2005, 6, 5, 4)
The function works by figuring out the weekday of the first of the month–relatively easy to do since the first is always day 1. Once I know what weekday the 1st falls on, the code then calculates how many days it needs to add to that date to get to the first occurrence of the desired weekday in the month. In my example that generated the date for the fourth Thursday in June, the first day of the month will be 1 June 2005, which is a Wednesday. This expression tells me the number of days to add to the 1st of June to get the first Thursday in June:
(WhatWeekDay - Weekday(dtmFirstOfMonth) + 7) Mod 7
The mechanics of this calculation are interesting. The function call Weekday(dtmFirstOfMonth) for June 2005 returns 4 for Wednesday. I’m interested in Thursday (which is day 5), so the formula (5 – 4 + 7) Mod 7 gives me 1–indicating that I need to add one day to get the first Thursday in the month (the date 2 June 2005). Once I know the first Thursday of the month, it’s easy to add seven days at a time to arrive at the specific week in the month.
However, this is only part of the requirement, because sometimes we don’t actually know which specific week of the month it is we need. For instance, in Canada, Victoria Day is the second-to-last Monday in May. Depending on what the first day of May is, the second-to-last Monday will sometimes be the third Monday, sometimes the fourth Monday.
It’s possible to do a set of calculations similar to the preceding one to figure out the last occurrence of a particular day in a month, and work toward the front of the month to arrive at the desired date. The first step is to change the definition of WhatWeekdayOfMonth so that -1 means the last occurrence of that weekday in the month, -2 means the second-to-last occurrence, and so on. The final function will then look like this:
Function GetDate( _
WhatYear As Long, _
WhatMonth As Long, _
WhatWeekDay As Long, _
WhatWeekDayOfMonth As Long _
) As Date
Dim dtmFirstOfMonth As Date
Dim dtmLastOfMonth As Date
If WhatWeekDayOfMonth > 0 And _
WhatWeekDayOfMonth < 6 Then
dtmFirstOfMonth = _
DateSerial(WhatYear, WhatMonth, 1)
GetDate = DateAdd("w", _
(WhatWeekDayOfMonth - 1) * 7, _
DateAdd("d", _
(WhatWeekDay - _
Weekday(dtmFirstOfMonth) + 7) Mod 7, _
dtmFirstOfMonth) _
)
If Month(GetDate) > WhatMonth Then
GetDate = DateAdd("d", -7, GetDate)
End If
ElseIf WhatWeekDayOfMonth > -6 And _
WhatWeekDayOfMonth < 0 Then
dtmLastOfMonth = _
DateSerial(WhatYear, (WhatMonth + 1), 0)
GetDate = DateAdd("w", _
(WhatWeekDayOfMonth + 1) * 7, _
DateAdd("d", _
0 - ((Weekday(dtmLastOfMonth) - _
WhatWeekDay + 7) Mod 7), _
dtmLastOfMonth) _
)
If Month(GetDate) < WhatMonth Then
GetDate = DateAdd("d", 7, GetDate)
End If
End If
End Function
With relative dates handled, the final complication is to calculate holidays that have their own rules. I decided to tackle the problem of Easter, even though I realized that the solution for this date isn’t going to be applicable to any other holidays.
The answer was surprisingly easy to find: While it seemed unlikely to me that a rule such as “the first Sunday after the first full moon after the vernal equinox” can be expressed mathematically, a quick Google revealed that there are, in fact, numerous algorithms to calculate when Easter Sunday falls in a given year. I’m not going to try and explain how any of the algorithms I found work, nor am I going to guarantee that they’re completely accurate. However, if you take a look in the accompanying database, you’ll find four different functions that will return the date of Easter Sunday, given the year. (I’ve also included URL references where you can learn more about the algorithms.)
Now that we have the basic building blocks, let’s see how they can be combined to solve the problem at hand.
The approach that I decided to take is to create a table with the fields shown in Figure 1.
Figure 1
For any given holiday, the table will have one of the following combinations:
- Values for FixedMonth and FixedMonthDay
- Values for FixedMonth, FixedWeekday, and FixedWeekdayofMonth
- Value for RelativeToEasterSunday
Each of the fields FixedMonth, FixedMonthDay, FixedWeekday, FixedWeekdayofMonth, and RelativeToEasterSunday must be capable of accepting Null values, since we know that not all of the fields can be defined simultaneously for any given holiday (the reason for having RelativeToEasterSunday is because, in many areas, it’s Good Friday and/or Easter Monday that’s the holiday, not Easter itself).
I then created a function that can handle having any one of those combinations of parameters passed:
Function ComputeHoliday( _ WhatYear As Long, _ WhatMonth As Variant, _ WhatMonthDay As Variant, _ WhatWeekday As Variant, _ WhatWeekdayOfMonth As Variant, _ RelativeToEasterSunday As Variant _ ) As Date If (IsNull(WhatMonth) = False And _ IsNull(WhatMonthDay) = False) Then ComputedHoliday = DateSerial( _ WhatYear, _ WhatMonth, _ WhatMonthDay _ ) ElseIf (IsNull(WhatMonth) = False And _ IsNull(WhatWeekday) = False And _ IsNull(WhatWeekdayOfMonth) = False) Then ComputedHoliday = GetDate( _ WhatYear, _ CLng(WhatMonth), _ CLng(WhatWeekday), _ CLng(WhatWeekdayOfMonth) _ ) ElseIf _ IsNull(RelativeToEasterSunday) = False Then ComputedHoliday = DateAdd( _ "d", _ RelativeToEasterSunday, _ GetEaster_ButchersAlgorithm(WhatYear) _ ) Else ComputedHoliday = 0 End If End Function
With the exception of WhatYear, all of the parameters have been set as Variants. This is because I want to be able to use this function in a query that passes the fields from each row, and some of the fields are going to be Null on each row.
You may be wondering about the last two fields in the table, AdjustIfSaturday and AdjustIfSunday. Often the date on which a particular holiday falls and when it’s celebrated aren’t the same (Easter is just the most ancient example). If, for example, Christmas falls on a Saturday, it’s usual to have Friday, December 24 as the holiday. These two fields allow me to adjust the calculated date to compensate for these conditions. Note that only those holidays in category 1 (holidays that occur on the same date each year) will require this adjustment.
As an example of how this all works, I’ll talk about some of the federal holidays in Canada. The specific holidays I’ve included are listed in Table 1.
Table 1. Canadian holidays celebrated in all provinces (other holidays are on a province-by-province basis).
| Holiday | Date |
| New Year’s Day | January 1 |
| Good Friday | Two days before Easter Sunday |
| Victoria Day | Second-to-last Monday in May |
| Canada Day | July 1 |
| Labour Day | First Monday in September |
| Thanksgiving Day | Second Monday in October |
| Christmas Day | December 25 |
| Boxing Day | December 26 |
Figure 2 shows how I store that information in the Holidays table.
Figure 2
Since Victoria Day, Labour Day, and Thanksgiving Day are always on Mondays and Good Friday is always on a Friday, there’s no need to specify Adjustments for those occasions when the holidays fall on Saturdays or Sundays.
Where I live, if New Year’s Day falls on the weekend, the holiday is observed after the date: Canadians will never get a holiday for New Year’s Day in the previous year. That means that the adjustment columns for New Year’s Day must always add to the date for New Year’s Day–in other words, if New Year’s Day falls on a Saturday, the code should add two days, or if it falls on a Sunday add one day, to get the following Monday.
You may be confused by the adjustment dates I have for Christmas and Boxing Day. If Christmas falls on a Sunday, Boxing Day will obviously fall on a Monday. However, I can’t simply say to adjust Christmas by adding a day to it, because whenever Christmas is on a Sunday, I’ll have two holidays on the following Monday, December 26. Consequently, I cheated. I set up my table to add two days to Christmas, so that it will appear that we’ll celebrate Christmas on December 27. Usually it would be said when we take December 26 as a holiday that we’re celebrating “Christmas” and when we take December 27 off we’re celebrating “Boxing Day.” But the net effect is the same (two days off!), so I figured this was a reasonable simplifying assumption.
Similarly, if Boxing Day occurs on a Saturday, I subtract two days, not one, so that it appears that the holiday for Boxing Day is being celebrated on December 24, and Christmas on December 25. Figure 3 and Figure 4 show how the holiday dates get calculated for 2005 and 2006 using the data from Figure 2.
Figure 3
Figure 4
I have to make a couple of confessions about the data contained in the accompanying database. You may have noticed that I haven’t filled in the Adjust dates for most of the holidays. That’s because how such adjustments are made is usually a local decision, and I simply don’t have the necessary information to be able to fully populate the table!
Finally, my apologies for any errors and omissions in the table. I only meant it to be representative so that you could get some idea of how it could be used for your particular situation. Holidays tend to be defined at the provincial or state level, and I didn’t want to include that much data.
