Jump to content

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


Recommended Posts

  • Newbies

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.)

Link to post
Share on other sites

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

 

 

Link to post
Share on other sites
  • Newbies

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.

Link to post
Share on other sites

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 by comment
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.