Need Custom Function to determine a date skipping weekends and bank holidays

[wfcperrine12]

I have searched the web for a Custom Function that solves this to no avail…  There are many CF’s that “almost” do what I need but nothing as explained below.

OBJECTIVE:

Calculate a DATE x days AFTER a given date skipping weekends AND bank holidays.

 

GIVEN:

StartDate

NumbOfDays

Result Desired = Date that skips weekends and bank holidays.

 

Any year’s HOLIDAY dates can be gleaned from pre-existing Custom Function “HolidayListByYear (yearNumber)” written by Jonathan Mickelson at www.briandunning.com (also uses another Custom Function he wrote called “DateByDayOccur”).

 

Banking holidays should always include:

* When a holiday falls on a Saturday, it is usually observed on the preceding Friday. When the holiday falls on a Sunday, it is usually observed on the following Monday.
New Years Day	Jan 1
Martin Luther King Day	Jan 20
President’s Day	Feb 17
Memorial Day	May 25
Independence Day	July 4
Independence Day (observed)	?
Labor Day	First Monday in September
Columbus Day	Second Monday in October
Veterans Day	Nov 11
Thanksgiving Day	4th Thursday in November
Christmas Eve	December 24
Christmas Day	December 25
New Year’s Eve	December 31

 

 

EXPECTED RESULT EXAMPLE 1:

StartDate = 12/30/2020

NumbOfDays = 2

Result would be= 1/3/2021

NOTE: Must skip Dec 31 (New Years Eve/weekend day), Jan 1 (New Years Day/weekend day)

 

 

EXPECTED RESULT EXAMPLE 2:

StartDate = 1/15/2021

NumbOfDays = 2

Result would be= 1/20/2021

NOTE: Must skip weekend AND Jan 18, MLK Day

 

 

EXPECTED RESULT EXAMPLE 3:

StartDate = 7/2/2021

NumbOfDays = 2

Result would be= 7/7/2021

NOTE: Must skip weekend and July 5 (July 5th is the observed day for Sunday, July 4, 2021)

 

SPECIAL NOTE:  The custom function must be able to handle year crossovers such as EXAMPLE 1 above (going from 2020 into 2021.)

[comment]

I believe the customary way to handle this is to precompute a list of holidays far enough into the future to cover the longest possible number of days to add. There aren't that many of them, so you could easily generate a table of holidays going many years into the future.

Once you have such list/table, you can proceed as shown here:
https://fmforums.com/topic/54940-non-holiday-weekday/?do=findComment&comment=259555&_rid=72594

 

 

[wfcperrine12]

Ya- that would be my fall-back position but it seems like someone out there could just modify a pre-existing Custom Function to make this work.  Other people have already written most of the parts- I just don't have the skills to put it together.

[comment]

it would be very awkward/inefficient to do it the way you propose (if I understand correctly what you propose). Since the method requires testing every consecutive date for being a holiday, and - as you yourself pointed out - the dates can be in different years, you would need to generate the entire list of the year's holidays anew for every date in the loop. Or construct an outer loop to replace the list when the year changes.

But if you want, you could easily take my function and replace the listOfHolidays parameter with a call to another custom function that generates a list of holidays for the year of startDate. 

 

Edited December 25, 2020 by comment