How to Generate a list of Common Holidays for any Year

Crystal Reports Training by Ken Hamady, MS, Reporting and Training Nationwide

Free Crystal Reports formula examples from KenHamady.com

To learn the techniques used in these formulas get:

The Expert's Guide to Crystal Reports Formulas
and
Expert Techniques for Crystal Reports I, II & III

How to Generate a list of Common (US) Holidays for any Year:

(Or use the Canadian 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 holidays for any year automatically. 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. Mike has written a separate formula for calculating Good Friday and Easter which then used inside this formula.   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.

numberVar yr := {@yr}; // The year for which you need Holidays
dateVar array holidays;
    redim holidays [8];

holidays [1] := CDate (yr, 1, 1);     // New Years day
holidays [2] := {@GoodFridayHoliday}; // Good Friday
holidays [3] := CDate (DateAdd ("d", 1 - DayOfWeek (CDate (yr, 5, 31), crMonday), CDate (yr, 5, 31) ) );
                                      // Memorial Day (last Mon in May)
holidays [4] := CDate (yr, 7, 4);     // July 4 (Independence Day)
holidays [5] := CDate (DateAdd ("d", 7 - DayOfWeek (CDate (yr, 9, 1) , crTuesday), CDate (yr, 9, 1) ) );
                                      // Labor Day (first Mon in Sept)
holidays [6] := Date ( yr , 11 , 3) - DayOfWeek ( Date ( yr , 11 , 3) ) + 26;
                                      // Thanksgiving (4th Thurs. in Nov)
holidays [7] := CDate (yr, 12, 25);   // Merry Christmas
holidays [8] := CDate (yr + 1, 1, 1); // New Years next year (may fall on the 31st of this year)

// check for holidays that fall on weekend: New Years, July 4, Christmas

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", -1, holidays [1]));

if DayOfWeek (holidays [4]) = crSunday then
    holidays [4] := CDate (DateAdd ("d", 1, holidays [4]))     // July 4th
else if DayOfWeek (holidays [4]) = crSaturday then
    holidays [4] := CDate (DateAdd ("d", -1, holidays [4]));

if DayOfWeek (holidays [7]) = crSunday then
    holidays [7] := CDate (DateAdd ("d", 1, holidays [7]))     // Christmas
else if DayOfWeek (holidays [7]) = crSaturday then
    holidays [7] := CDate (DateAdd ("d", -1, holidays [7]));

if DayOfWeek (holidays [8]) = crSunday then
    holidays [8] := CDate (DateAdd ("d", 1, holidays [8]))     // New Years Day for next year
else if DayOfWeek (holidays [8]) = crSaturday then
    holidays [8] := CDate (DateAdd ("d", -1, holidays [8]));

yr // return yr