Tip: Display all US legal holidays for a given year

In this post, we create an app that displays all legal US holidays for a given year. The app adapts a formula created for Microsoft Excel and uses new functions introduced with our new formula engine, like FORMATDATE, FILTER and TEXTJOIN.

Legal holidays are days established by law that are not considered to be working days. While different countries use different legal holidays, we’ll only consider legal holidays in the United States for this tip.

What if you want to display a list of all legal holidays for a given year? That used to be difficult to do with Calcapp, but with our new formula engine and its support for arrays, this is actually fairly straight-forward.

This tip introduces a simple app where the year is entered in the number field Year and a list of the legal holidays is presented in the text field LegalHolidays. Here’s an embedded version of the app, which you can also run as a stand-alone app:

If you’re familiar with spreadsheets in general, and Microsoft Excel 2021 in particular, Calcapp’s formula language should be very easy to pick up. You are generally able to use Excel resources to learn about solving problems and apply those solutions in Calcapp.

For this problem, that’s exactly what we did. We found an article discussing US public holidays on the website of Extend Office, which lists not only the public holidays, but also formulas for calculating them for a given year.

Some legal holidays fall on particular dates every year, like New Year’s Day (January 1). Other holidays are trickier, like Labor Day, which falls on the first Monday in September.

To calculate the date for Labor Day, we’ll use this formula:

DATE(Year, 9, 1) + CHOOSE(WEEKDAY(DATE(Year, 9, 1)), 1, 0, 6, 5, 4, 3, 2)DATE(Year; 9; 1) + CHOOSE(WEEKDAY(DATE(Year; 9; 1)); 1; 0; 6; 5; 4; 3; 2)

The first part, DATE(Year, 9, 1)DATE(Year; 9; 1), returns September 1 of the year indicated by the Year field. We then add a number of days to that date, calculated by this part of the formula:

CHOOSE(WEEKDAY(DATE(Year, 9, 1)), 1, 0, 6, 5, 4, 3, 2)CHOOSE(WEEKDAY(DATE(Year; 9; 1)); 1; 0; 6; 5; 4; 3; 2)

The WEEKDAY function returns a number corresponding to the day of the week for a given date (in this case, September 1). When no second parameter is given to WEEKDAY, 1 is returned for Sunday, 2 is returned for Monday, and so on.

CHOOSE returns the parameter indicated by its first parameter. CHOOSE(2, "one", "two", "three")CHOOSE(2; "one"; "two"; "three") returns “two”, because the first parameter indicates that of the remaining parameters, the second one should be chosen.

In the formula above, the value returned from WEEKDAY is fed into CHOOSE. If September 1 is a Sunday, 1 is added. Why? Because Labor Day always falls on a Monday. If September 1 is already a Monday, 0 is added, and if September 1 is a Tuesday, 6 is added, so that the coming Monday is calculated.

To calculate an array containing the legal holidays of a certain year, we use this lengthy formula:

DATE(Year, 1, 1), DATE(Year, 1, 1) + 14 + CHOOSE(WEEKDAY(DATE(Year, 1, 1)), 1, 0, 6, 5, 4, 3, 2), DATE(Year, 2, 1) + 14 + CHOOSE(WEEKDAY(DATE(Year, 2, 1)), 1, 0, 6, 5, 4, 3, 2), DATE(Year, 6, 1) - WEEKDAY(DATE(Year, 6, 6)), DATE(Year, 6, 19), DATE(Year, 7, 4), DATE(Year, 9, 1) + CHOOSE(WEEKDAY(DATE(Year, 9, 1)), 1, 0, 6, 5, 4, 3, 2), DATE(Year, 10, 1) + 7 + CHOOSE(WEEKDAY(DATE(Year, 10, 1)), 1, 0, 6, 5, 4, 3, 2), DATE(Year, 11, 11), DATE(Year, 11, 1) + 21 + CHOOSE(WEEKDAY(DATE(Year, 11, 1)), 4, 3, 2, 1, 0, 6, 5), DATE(Year, 12, 25) }DATE(Year; 1; 1); DATE(Year; 1; 1) + 14 + CHOOSE(WEEKDAY(DATE(Year; 1; 1)); 1; 0; 6; 5; 4; 3; 2); DATE(Year; 2; 1) + 14 + CHOOSE(WEEKDAY(DATE(Year; 2; 1)); 1; 0; 6; 5; 4; 3; 2); DATE(Year; 6; 1) - WEEKDAY(DATE(Year; 6; 6)); DATE(Year; 6; 19); DATE(Year; 7; 4); DATE(Year; 9; 1) + CHOOSE(WEEKDAY(DATE(Year; 9; 1)); 1; 0; 6; 5; 4; 3; 2); DATE(Year; 10; 1) + 7 + CHOOSE(WEEKDAY(DATE(Year; 10; 1)); 1; 0; 6; 5; 4; 3; 2); DATE(Year; 11; 11); DATE(Year; 11; 1) + 21 + CHOOSE(WEEKDAY(DATE(Year; 11; 1)); 4; 3; 2; 1; 0; 6; 5); DATE(Year; 12; 25) }

This formula has been adapted from the Extend Office article, with Juneteenth added (which was made a federal holiday only last year).

The formula above includes all dates, regardless of whether they fall on a Saturday or a Sunday, though. We can easily filter out days that fall on weekends using FILTER.

There is a version of FILTER that takes an array as its first parameter and a formula fragment as its second parameter, which we’ll use here. The formula fragment has access to the element under consideration under the Element name, and is expected to return TRUE if the element should be kept and FALSE otherwise.

This formula returns the array { 1, 3 }{ 1; 3 }, as only odd elements are kept:

FILTER({ 1, 2, 3 }, ISODD(Element))FILTER({ 1; 2; 3 }; ISODD(Element))

To determine if a date should be kept, we’ll use the WEEKDAY function. When invoked with 2 as its second parameter, 1 is returned for Monday, 2 is returned for Tuesday, 5 is returned for Friday, and so on. That means that we can check the return value to see if it is less than or equal to 5 to filter out dates that fall on a weekend:

FILTER(…, WEEKDAY(Element, 2) <= 5)FILTER(…, WEEKDAY(Element; 2) <= 5)

If we plug in the long array formula fragment with the dates in place of ..., we finally have our formula!

Now, we just need to present it in a text field. First, we’ll use the FORMATDATE function to format all dates of the array as text. When FORMATDATE is invoked with an array, it is invoked once for every array element, with the results collected as an array. This formula does what we need:

FORMATDATE(FILTER(…, WEEKDAY(Element, 2) <= 5), DateFormat.FullDayInWeek)FORMATDATE(FILTER(…, WEEKDAY(Element; 2) <= 5); DateFormat,FullDayInWeek)

Now, instead of an array containing sequential serial numbers, representing dates of legal holidays, we have a text array containing the formatted versions of these dates. In order to present a value in a text field, though, we need a plain text string.

To create said text string, TEXTJOIN is used. It takes an array of text strings and joins all the text strings together in a single text string. Its third parameter is the array to join together and its first parameter is the delimiter, separating the elements.

The delimiter is often set to something like “, “ (which uses a comma to separate array elements), but here, we want each date to appear on its own line. (The text field must be configured to display multiple lines by toggling the Multiple lines property in the inspector when the text field is selected in Calcapp Creator.)

To that end, we use the NEWLINE function in place of the delimiter, which returns a line break:

TEXTJOIN(NEWLINE(), FALSE, FORMATDATE(FILTER(…, WEEKDAY(Element, 2) <= 5), DateFormat.FullDayInWeek))TEXTJOIN(NEWLINE(); FALSE; FORMATDATE(FILTER(…, WEEKDAY(Element; 2) <= 5); DateFormat,FullDayInWeek))

The last step is to plug in the long array in place of .... Here is the complete formula, which returns a text string representing all legal US holidays of a certain year:

TEXTJOIN(NEWLINE(), FALSE, FORMATDATE(FILTER({ DATE(Year, 1, 1), DATE(Year, 1, 1) + 14 + CHOOSE(WEEKDAY(DATE(Year, 1, 1)), 1, 0, 6, 5, 4, 3, 2), DATE(Year, 2, 1) + 14 + CHOOSE(WEEKDAY(DATE(Year, 2, 1)), 1, 0, 6, 5, 4, 3, 2), DATE(Year, 6, 1) - WEEKDAY(DATE(Year, 6, 6)), DATE(Year, 6, 19), DATE(Year, 7, 4), DATE(Year, 9, 1) + CHOOSE(WEEKDAY(DATE(Year, 9, 1)), 1, 0, 6, 5, 4, 3, 2), DATE(Year, 10, 1) + 7 + CHOOSE(WEEKDAY(DATE(Year, 10, 1)), 1, 0, 6, 5, 4, 3, 2), DATE(Year, 11, 11), DATE(Year, 11, 1) + 21 + CHOOSE(WEEKDAY(DATE(Year, 11, 1)), 4, 3, 2, 1, 0, 6, 5), DATE(Year, 12, 25) }, WEEKDAY(Element, 2) <= 5), DateFormat.FullDayInWeek))TEXTJOIN(NEWLINE(); FALSE; FORMATDATE(FILTER({ DATE(Year; 1; 1); DATE(Year; 1; 1) + 14 + CHOOSE(WEEKDAY(DATE(Year; 1; 1)); 1; 0; 6; 5; 4; 3; 2); DATE(Year; 2; 1) + 14 + CHOOSE(WEEKDAY(DATE(Year; 2; 1)); 1; 0; 6; 5; 4; 3; 2); DATE(Year; 6; 1) - WEEKDAY(DATE(Year; 6; 6)); DATE(Year; 6; 19); DATE(Year; 7; 4); DATE(Year; 9; 1) + CHOOSE(WEEKDAY(DATE(Year; 9; 1)); 1; 0; 6; 5; 4; 3; 2); DATE(Year; 10; 1) + 7 + CHOOSE(WEEKDAY(DATE(Year; 10; 1)); 1; 0; 6; 5; 4; 3; 2); DATE(Year; 11; 11); DATE(Year; 11; 1) + 21 + CHOOSE(WEEKDAY(DATE(Year; 11; 1)); 4; 3; 2; 1; 0; 6; 5); DATE(Year; 12; 25) }; WEEKDAY(Element; 2) <= 5); DateFormat,FullDayInWeek))
« Tip: Custom formatting of dates Tip: Summarize switch field selections in a single sentence »