Jump to content

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

Recommended Posts

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.


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





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




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)




StartDate = 1/15/2021

NumbOfDays = 2

Result would be= 1/20/2021

NOTE: Must skip weekend AND Jan 18, MLK Day




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:



Link to post
Share on other sites

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
  • Similar Content

    • By Sinky
      Hi FM people!
      I have one problem which is probably simple to solve but so far solution eluded me.
      I have a dropdown text field which offers a list of years (Table::Years). This is global field.
      I have very simple find script:
      Enter Find Mode [Pause: Off]
      Set Field [Table::Order Date; Table::Years]
      Constrain Found Set [ ]
      Table::Order Date is a date field.
      Idea is to perform find according to the value chosen from a dropdown list. However, this does not work. I'm getting "provided find criteria not valid".
      But, if I enter manually one of the years from the list, It works. Same is valid if I write down a year in the script e.g. Set Field [Table::Order Date; 1980].
      Set Field [Table::Order Date; Table::Years] itself works when in browse mode, but in find mode, nope
      This is I guess a format issue, so I tried several combinations changing fields to text or date but without success.
      Any help is appreciated
    • By Poruchan
      I’d like to show days since last race for a horse where the first race returns 0. I played around with ‘last’ and ‘GetNthRecord’ but something went wrong. I had some problems with races where the horse was scratched, but I can live with that. 
    • By ash1474
      Hi, I am using this calculation in portal which calculates days in portal  Date - (GetNthRecord ( Date ; Get ( RecordNumber ) - 1 ) )
      how can i modify or do something else to avoid (?) in the first row.  Sample file attached for any modification/help.
      Thanks for any help
    • By Ponderosa
      So there's probably something simple, but do you guys know of a way to automate dates from one record to the next? Each record date needs to be seven days after the previous one, and I'd like for my client not to have to enter it manually. I tried doing
      Let ( Today = ( ReleaseDate ) ;

      Date ( Month ( Today ) ; Day ( Today ) + 7 ; Year ( Today ) )

      and some others, but none appear to automatically fill in. Help!
    • By naio
      My solution is build according to the 'separation model', so I would like to keep control of the local file containing the layout and presentation of the data hosted in the server.
      I wonder if I could have a script to control wether the local file gets its structure, layout or scripting modified, so I need a function to control when a change to the file has been made before giving it a version number. When the file gets open only to work with the hosted data it's not a new version.
  • Create New...

Important Information

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