# Formula that calculates all US federal holidays

Tuesday 7 November 2017 @ 4:33 pm

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.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)