The gist for this lambda function can be found here.
You can download a workbook with example definitions of relative and fixed holidays here.
The goal
When working with dates in Excel, it’s sometimes useful to have an accurate list of the public holidays in a given year so we can calculate (for example) the working days between two dates.
We might also want to create a simple attendance calendar and need to know which dates should be excluded.
So, the goal here is:Create a lambda function that will return a list of holidays for an arbitrary year based on some year-independent definitions of which dates should be holidays
A solution
Here’s a lambda called GETHOLIDAYS:
=LAMBDA(year, [relative_holidays], [fixed_holidays],
IF(AND(ISOMITTED(relative_holidays),ISOMITTED(fixed_holidays)),NA(),
LET(
_yr,IF(OR(NOT(ISNUMBER(year)),LEN(year)<>4),NA(),year),
_cleanup,LAMBDA(hols,IFS(
ISOMITTED(hols),{0,0,0,"None"},
COLUMNS(hols)<>4,NA(),
TRUE,FILTER(hols,INDEX(hols,,4)<>"")
)
),
/*Relative holidays*/
_rh,_cleanup(relative_holidays),
_rhm, INDEX(_rh, , 3),
_rd, DATE(_yr, _rhm, 1 + 7 * INDEX(_rh, , 1)) - WEEKDAY(DATE(_yr, _rhm, 8 - INDEX(_rh, , 2))),
_r_out, CHOOSE({1, 2}, _rd, INDEX(_rh, , 4)),
/*Fixed holidays*/
_fh,_cleanup(fixed_holidays),
_doubles,LAMBDA(hols,NOT(ISERROR(XMATCH(hols-1,hols)))),
/*{option,weekday,weekend increment,double increment}*/
_defincr,{1,7,-1,2;
1,1,1,1;
1,2,0,1;
2,7,2,3;
2,1,1,2;
2,2,0,1;
3,7,-1,-2;
3,1,-2,-3;
3,2,0,0;
0,7,0,0;
0,1,0,0},
_fd_orig,DATE(_yr,INDEX(_fh,,1),INDEX(_fh,,2)),
_get_incrs,LAMBDA(col,XLOOKUP(INDEX(_fh,,3)&"-"&WEEKDAY(_fd_orig),INDEX(_defincr,,1)&"-"&INDEX(_defincr,,2),INDEX(_defincr,,col),0)),
_fd,IF(
_doubles(_fd_orig),_fd_orig + _get_incrs(4),
_fd_orig + _get_incrs(3)
),
_f_out, CHOOSE({1, 2}, _fd, INDEX(_fh,,4)),
_out, MAKEARRAY(
ROWS(_r_out) + ROWS(_f_out),
2,
LAMBDA(r, c,
IF(
r <= ROWS(_r_out),
INDEX(_r_out, r, c),
INDEX(_f_out, r - ROWS(_r_out), c)
)
)
),
_output,SORT(FILTER(_out,INDEX(_out,,2)<>"None")),
_output
)
)
)
Here’s what it does:
How it works
GETHOLIDAYS takes 3 parameters:
-
- year – the four-digit year for which we want to calculate holidays according to the provided lists
- [relative_holidays] – OPTIONAL if fixed_holidays is provided – a four-column array of data where the columns are:
- The Nth week of the month. Positive non-zero integers from 1 to 5 represent the nth week in the month specified in the third column. If 0, this represents the last week of the month prior to the month in the month column. So, {0,2,6,”Last Monday in May”} is the last Monday in the month prior to June. Similarly, {-1,2,6,”Second-to-last Monday in May”} will be the Monday prior to the last Monday in the month prior to June
- The weekday of the Nth week of the month. The week is Sunday=1, Monday=2, … , Saturday=7
- The number from 1 to 12 representing the month. See note under column 1 regarding “Last X of Y”
- The description of the holiday
- [fixed_holidays] – OPTIONAL if relative_holidays is provided – a four-column array of data where the columns are:
- The month of the holiday
- The day of the holiday
- How to shift the date of the observed holiday if the official holiday date lands on a weekend. This column should have one of the following values:
name definition 1 “Split” – if the official holiday falls on a Saturday, the observed holiday should be on Friday. If the official holiday falls on a Sunday, the observed holiday should be on Monday. 2 “Forward” – if the official holiday falls on either Saturday or Sunday, the observed holiday should be on Monday. 3 “Backward” – if the official holiday falls on either Saturday or Sunday, the observed holiday should be on Friday. 0 “None” – the observed holiday should be on whatever day the official holiday falls. Note that there is special behavior if the holiday represents the second in a so-called “double” holiday (Boxing Day is the 2nd day in a double holiday of Christmas + Boxing Day in the UK).
Generally, you should define the option above the same way for both holidays in a double holiday. That said, if the second day of {Christmas,Boxing Day} falls on a Monday, and the option is “forward”, then the function will move Boxing Day to Tuesday (because Christmas Day will have been moved from Sunday to Monday).
However, if Boxing Day falls on Monday and the option is “backward”, then Christmas will be moved to Friday and Boxing Day will remain on Monday (since it is not a weekend).
If Christmas falls on Saturday and Boxing Day on Sunday and the option is “forward”, then the observed holidays will be Monday and Tuesday. If “backward”, then the observed holidays will be Thursday and Friday. If “split”, then Friday and Monday and if “none”, then the holidays will not be moved.
- The description of the holiday
Please note that you must provide relative_holidays or fixed_holidays or both. If you provide neither, the function will return #NA!
If you’d like to use this now, you can grab the code from the link at the top of the post. If you’d like to understand how it works so you can modify it for your own specific needs, please read on.
Let’s break it down
We start by checking if both relative_holidays and fixed_holidays are omitted (not provided).
=LAMBDA(year, [relative_holidays], [fixed_holidays],
IF(AND(ISOMITTED(relative_holidays),ISOMITTED(fixed_holidays)),NA(),
LET(
If they are both omitted, then the function returns #NA!. If at least one of these lists is provided, then we define variables using LET:
LET(
_yr,IF(OR(NOT(ISNUMBER(year)),LEN(year)<>4),NA(),year),
_cleanup,LAMBDA(hols,IFS(
ISOMITTED(hols),{0,0,0,"None"},
COLUMNS(hols)<>4,NA(),
TRUE,FILTER(hols,INDEX(hols,,4)<>"")
)
),
- _yr – we check that the year parameter is a four digit number. If it is either not a number or is not four digits, then set _yr to NA(), otherwise set it to year
- _cleanup – here we define a helper lambda which will check if a list of holidays is provided. If it is not, then this lambda returns a single-row array with some default values as shown. If the array is provided but it doesn’t have 4 columns, then this helper lambda returns NA(). Otherwise, it returns all rows from the passed list of holidays with a non-empty holiday description
Some calculations to create the list of dates for so-called “relative holidays”:
/*Relative holidays*/
_rh,_cleanup(relative_holidays),
_rhm, INDEX(_rh, , 3),
_rd, DATE(_yr, _rhm, 1 + 7 * INDEX(_rh, , 1)) - WEEKDAY(DATE(_yr, _rhm, 8 - INDEX(_rh, , 2))),
_r_out, CHOOSE({1, 2}, _rd, INDEX(_rh, , 4)),
- _rh – we apply the _cleanup lambda to the relative_holidays parameter
- _rhm – here we get the column of month numbers from the list of relative holidays
- _rd – here we calculate the dates of each of the rows in the relative holidays list
- _r_out – here we create the output list of holiday dates based on the relative holidays list. The output consists of the dates in the first column and the description of the holiday in the second column
And calculations to create the list of dates for the “fixed holidays”:
/*Fixed holidays*/
_fh,_cleanup(fixed_holidays),
_doubles,LAMBDA(hols,NOT(ISERROR(XMATCH(hols-1,hols)))),
/*{option,weekday,weekend increment,double increment}*/
_defincr,{1,7,-1,2;
1,1,1,1;
1,2,0,1;
2,7,2,3;
2,1,1,2;
2,2,0,1;
3,7,-1,-2;
3,1,-2,-3;
3,2,0,0;
0,7,0,0;
0,1,0,0},
_fd_orig,DATE(_yr,INDEX(_fh,,1),INDEX(_fh,,2)),
_get_incrs,LAMBDA(col,XLOOKUP(INDEX(_fh,,3)&"-"&WEEKDAY(_fd_orig),INDEX(_defincr,,1)&"-"&INDEX(_defincr,,2),INDEX(_defincr,,col),0)),
_fd,IF(
_doubles(_fd_orig),_fd_orig + _get_incrs(4),
_fd_orig + _get_incrs(3)
),
_f_out, CHOOSE({1, 2}, _fd, INDEX(_fh,,4)),
- _fh – we apply the _cleanup lambda to the fixed_holidays parameter
- _doubles – here we define another helper lambda. In this case, we check whether each date in a list of holidays is one day after another date in the same list. If it is, we consider it the second date in a so-called “double” (such as Boxing Day) and return TRUE. Otherwise return FALSE.
- _defincr – this array defines the increments to apply to different combinations of “weekend behavior” (column 3 in the ‘fixed holidays’ parameter) and weekday of the holiday. As an example, the first row is {1,7,-1,2}. The columns are:
- Weekend behavior – here using weekend behavior option 1 – “split”
- Weekday – here, 7=Saturday
- Increment – here, -1=Move the holiday back one day
- “Double” increment – here, move the holiday forward two days. This is necessary because, being the second date in a double holiday, the first date is already on the Friday, so the second date must, in the “split” option, be moved to the Monday
- _fd_orig – here we create a single-column array containing the official dates of the holidays in the fixed holidays parameter
- _get_incrs – here we define a helper lambda that will return a value from the _defincr array when passed a weekend behavior option and a weekday. This lambda is used in the definition of _fd to return either the weekend behavior increment (from column 3 of that array) or the “double” behavior increment (from column 4). Note that this function returns an increment of 0 if the official date is not on a weekend
- _fd – here we build the list of observed holiday dates for the fixed holidays. If the date is a “double”, then we add the “double” increment (from column 4 of _defincr) to the official date. If it is not a double, then we add the weekend behavior increment (from column 3 of _defincr) to the official date
- _f_out – we create the output list of holiday dates based on the fixed holidays list. The output consists of the dates in the first column and the description of the holiday in the second column
Finally,
_out, MAKEARRAY(
ROWS(_r_out) + ROWS(_f_out),
2,
LAMBDA(r, c,
IF(
r <= ROWS(_r_out),
INDEX(_r_out, r, c),
INDEX(_f_out, r - ROWS(_r_out), c)
)
)
),
_output,SORT(FILTER(_out,INDEX(_out,,2)<>"None")),
_output
)
)
)
- _out – we use MAKEARRAY to create a single output array with the fixed holidays underneath the relative holidays. This will be significantly simpler when the new VSTACK function is in General Availability.
- _output – finally, we filter out any rows where the description is “None” (which is created by the cleanup function if a list is omitted), then sort the output by date and return it to the calling function.
In summary
This post introduced the lambda function GETHOLIDAYS.
We saw how to calculate holiday dates for any year in Excel.
The function requires metadata in the form of at least one list of either relative holidays (where we define holidays such as “3rd Thursday in November”) or fixed holidays (where we define a month and year that the holiday falls on in each year, and an optional behavior to apply in case the official date falls on a weekend.
This was trickier than I thought it would be! I’ve not doubt it could be improved.
If you have suggestions, please let me know in the comments.
Thanks!