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

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

##### 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

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

##### 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

## Create an account

Register a new account

• ### 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
Hi,
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.
Thanks,
Paul
• 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
DateCalc.fmp12

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