I have several formulas that relate to “business days” or “business hours“. Most allow you to exclude holidays as long as you maintain a list (array) of holidays in a formula in the report. Years ago, one of my readers took the time to write a formula that would automatically load the holidays into an array based on the current year. I shared this on my site.

Recently one of my customers asked for a calculation to skip over all 10 of the observed federal holidays and that is when I realized that the formula mentioned above didn’t include all of the federal holidays. So I have written a special holidays array formula to calculate the federal holidays for any given year. In doing so I streamlined it a bit. You place the following formula in the report header of the report.

numberVar yr := {@Year}; // Current Year for Holidays
dateVar array holidays;
redim holidays [12];
holidays [1] := Date (yr, 1, 1); // New Years Day
holidays [2] := Date (yr, 1, 6) - DayOfWeek (Date (yr, 1, 6) ) + 16;
//Martin Luther King Jr. 3rd Monday in Jan
holidays [3] := Date (yr, 2, 6) - DayOfWeek (Date (yr, 2, 6) ) + 16;
//Presidents Day 3rd Monday in Feb
holidays [4] := Date (yr, 6, 1) - DayOfWeek (Date (yr, 5, 28) + 2);
// Memorial Day last Monday in May
holidays [5] := Date (yr, 7, 4); // Independence Day
holidays [6] := Date (yr, 9, 6) - DayOfWeek (Date (yr, 9, 6) ) + 2;
// Labor Day 1st Monday in Sept)
holidays [7] := Date (yr, 10, 6) - DayOfWeek (Date (yr, 10, 6) ) + 9;
//Columnbus Day 2nd Monday in Oct
holidays [8] := Date (yr, 11, 11); //Veterans Day
holidays [9] := Date (yr, 11, 3) - DayOfWeek (Date (yr, 11, 3) ) + 26;
//Thanksgiving 4th Thursday in Nov
holidays[10] := Date (yr, 12, 25); //Christmas Day
holidays [11] := Date (yr+1, 1, 1); //New Years Day (next year)
holidays [12] := Date (yr-1, 12, 25); //Christmas Day (last year)
Local NumberVar i;
For i := 1 to 12
do (
if DayofWeek (holidays [i]) = 1 then holidays [i] := holidays [i] +1 else
if DayofWeek (holidays [i]) = 7 then holidays [i] := holidays [i] -1 );
holidays[ 4] // Display one of the holidays

Then you can have other formulas refer to the array. The example below takes any date and if it happens to be on a weekend or holiday, moves it forward to the next business day:

WhilePrintingRecords;
DateVar Array Holidays;
DateVar Target:= {Your.Field}; //put your field or calculated date here
WHILE dayofweek (target) in [1,7] or (target in holidays)
Do target := target +1;
target

You can also refer to this array when using my business days calculation, instead of using a hard coded list of holidays.  If you have any questions about deploying this formula in one of your reports, let me know and we can schedule a session.