Brian Dunning's FileMaker Custom Functions

WorkingDateList ( StartDate ; EndDate ; HolidayList )

Returns a list of dates between StartDate and EndDate excluding any dates in HolidayList

  Average rating: 2.0 (3 votes) Log in to vote

Paul Bastin   Paul Bastin
Computech IT
Computech-it.co.uk

Share on Facebook Share on Twitter

  Sample input:
WorkingDateList (01/01/2018;10/01/2018;"04/01/2018¶05/01/2018¶06/01/2018¶08/01/2018" )
  Sample output:
01/01/2018
02/01/2018
03/01/2018
07/01/2018
09/01/2018
10/01/2018

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Returns a list of dates between StartDate and EndDate excluding any dates in HolidayList.
It does not exclude weekends automatically, you have to include them in the holiday list or remove them after calling the function.

NOTE: it requires another function to get the list of dates. There are several you could choose from, but I used this:

/*
GetDatesInRange (StartDate;EndDate)
Get a list of date in the range StartDtae to EndDate
*/

Case(

// Check parameters have been entered and make a sensible range

IsEmpty( StartDate ) or IsEmpty( EndDate );
"Please fill in both fields.";
StartDate > EndDate;
"Start date must be earlier than end date.";

// If we are good then return the list of dates

Case(
EndDate > StartDate;
GetDatesInRange( StartDate; EndDate - 1 ) & "¶" & EndDate;
EndDate
)
)

 

Comments

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.