How to Generate a list of Common Canadian Holidays for any Year:
(Or see the original US Version)One of my most requested formulas is the one that calculates the number of business days between two dates, minus weekends and holidays. It is designed to work with a separate formula that allows you to store your specific holidays. This "holidays" formula can contain many years worth of holidays in advance, but each years holidays must be entered. One of my readers, Mike Cook of Shepherd Hardware Products, decided to improve on this by writing a formula that creates the list of the (US) holidays for any year automatically. This was then updated to reflect Canadian holidays by Greg Fendall at net-linx Americas. The formula below generates the common holidays that do not occur on weekends. The exception is Good Friday, the Friday before Easter Sunday, because Easter is based on a Lunar calculation. Greg's formula still relies on Mike Cook's formula for calculating Good Friday. The only input required by this formula are a 4 digit numeric year labled {@yr} and the {@GoodFridayHoliday} formula.
// FUNCTION: @GetHolidays
// LANGUAGE: Crystal Reports 8.5 / Crystal syntax
// REQUIRES: Functions;@GoodFridayHoliday, @yr
// Variables:Global numberVar yr
// Global dateVar array holidays
// DESCRIPTION:
// This formula fills a global dateVar array holidays with the dates of the holidays
// for the year given in the global numberVar yr. It returns yr.
// CHANGE LOG:
// Written 6/28/03 by Mike Cook
// 12/30/03 mc: Fixed code for Labor Day, it was returning the wrong date, and added
// code to compute New Years Day for the following Year.
// 05/18/07 Greg Fendall: Changed to calculate Canadian Holidays. Some Holidays my differ
// depending on Province ie. Family Day Alberta.
numberVar yr := {@yr}; // The year for which you need Holidays
dateVar array holidays;
redim holidays [12];
holidays [1] := CDate (yr, 1, 1); // New Years day
holidays [2] := CDate (DateAdd ("d", -6 - DayOfWeek (CDate (yr, 2, 28), crMonday), CDate (yr, 2, 28) ) );
// Family Day Alberta (Third Mon in Feb)
holidays [3] := {@GoodFridayHoliday}; // Good Friday
holidays [4] := CDate (DateAdd ("d", -6 - DayOfWeek (CDate (yr, 5, 31), crMonday), CDate (yr, 5, 31) ) );
// Victoria Day (second last Mon in May)
holidays [5] := CDate (yr, 7, 1); // July 1 (Canada Day)
holidays [6] := CDate (DateAdd ("d", 7 - DayOfWeek (CDate (yr, 8, 1) , crTuesday), CDate (yr, 8, 1) ) );
// Civic Holiday (first Mon in Aug)
holidays [7] := CDate (DateAdd ("d", 7 - DayOfWeek (CDate (yr, 9, 1) , crTuesday), CDate (yr, 9, 1) ) );
// Labor Day (first Mon in Sept)
holidays [8] := CDate (DateAdd ("d", 14 - DayOfWeek (CDate (yr, 10, 1) , crTuesday), CDate (yr, 10, 1) ) );
// Thanksgiving (second Mon in Oct)
holidays [9] := CDate (yr, 11, 11); // Remeberance Day
holidays [10] := CDate (yr, 12, 25); // Merry Christmas
holidays [11] := CDate (yr, 12, 26); // Boxing Day
holidays [12] := CDate (yr + 1, 1, 1); // New Years next year
// check for holidays that fall on weekend: New Years, Canada Day, Remeberance Day, Christmas, Boxing Day
if DayOfWeek (holidays [1]) = crSunday then
holidays [1] := CDate (DateAdd ("d", 1, holidays [1])) // New Years Day
else if DayOfWeek (holidays [1]) = crSaturday then
holidays [1] := CDate (DateAdd ("d", 2, holidays [1]));
if DayOfWeek (holidays [5]) = crSunday then
holidays [5] := CDate (DateAdd ("d", 1, holidays [5])) // July 1
else if DayOfWeek (holidays [5]) = crSaturday then
holidays [5] := CDate (DateAdd ("d", 2, holidays [5]));
if DayOfWeek (holidays [9]) = crSunday then
holidays [9] := CDate (DateAdd ("d", 1, holidays [9])) // Remeberence Day
else if DayOfWeek (holidays [9]) = crSaturday then
holidays [9] := CDate (DateAdd ("d", 2, holidays [9]));
if DayOfWeek (holidays [10]) = crSunday then
holidays [10] := CDate (DateAdd ("d", 1, holidays [10])) // Christmas
else if DayOfWeek (holidays [10]) = crSaturday then
holidays [10] := CDate (DateAdd ("d", 2, holidays [10]));
if DayOfWeek (holidays [11]) = crSunday then
holidays [11] := CDate (DateAdd ("d", 2, holidays [11])) // Boxing Day
else if DayOfWeek (holidays [11]) = crSaturday then
holidays [11] := CDate (DateAdd ("d", 2, holidays [11]))
else if DayOfWeek (holidays [11]) = crMonday then
holidays [11] := CDate (DateAdd ("d", 1, holidays [11]));
if DayOfWeek (holidays [12]) = crSunday then
holidays [12] := CDate (DateAdd ("d", 1, holidays [12])) // New Years Day for next year
else if DayOfWeek (holidays [12]) = crSaturday then
holidays [12] := CDate (DateAdd ("d", 2, holidays [12]));
yr // return yr