Brian Dunning's FileMaker Custom Functions

WorkDaysList ( dateStart ; numberOfDays ; daysOfWeek ; excludeDatesList )

Creates a return delimited list of matching dates, from a starting date, for a set number of days, for select "days of the week" respecting any holidays.

  Average rating: 4.8 (30 votes) Log in to vote

Jonathan Mickelson   Jonathan Mickelson

Share on Facebook Share on Twitter

  Sample input:
WorkDaysList ( "12/30/2005" ; "3" ; "1234567" ; "12/31/2005¶01/01/2006¶" )
  Sample output:
12/30/2005¶
1/2/2006¶
1/3/2006¶

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

Function: WorkDaysList ( dateStart ; numberOfDays ; daysOfWeek ; excludeDatesList )

Description: This recursive function creates a return delimited list of valid dates from
a starting date, calculated from either a positive or negative number of days.
Allows for specific days of the week to be included/excluded, (ie. weekdays
only). Allows for a list of dates to exclude, such as holidays.

This is different from similar calculations that include both Start and End dates.
This function will return the dates of matching days until the "numberOfDays"
parameter is met. This means this function can determine end dates, or provide
a dates list containing a specific number of dates (form example: working days).

This can be used to easily:

- Generate a dynamic date driven value list, or a list of dates to perform some
action upon.

- Determine a work effort's start date given a desired "end Date" and negative
lead time (i.e 30 days required):
WorkingDays ( CompletionDueDate_field ; -30 ; "23456" ; excludeDatesList )

- Find an end date given a specific set of dates that are valid and not within
an exclusion list, allows for excluding holidays or a "Company Picnic" day.
NOTE: See "HolidayDatesByYear ( yearNumber )" function for dynamic
holiday calculations.

- Find the dates of all Sunday days within a set amount of time (perhaps a year).
WorkingDays ( 1/1/2014 ; 52 ; "1" ; "" )

- Allow comparisons of a date against a date in the resulting list:
Patterncount (
WorkDaysList ( someDate ; 30 ; "" ; "" ) ;
GetAsDate ( dateField )
) > 0

- Provide corroboration of results list by counting resulting values against input:
ValueCount ( WorkDaysList's Results ) = numberOfDays parameter provided

- Easily obtain just the final end date by grabbing the last entry on the
resulting list (or set the preference within the variables of the calc):
GetAsDate (
RightValues (
WorkDaysList ( someDate ; 30 ; "" ; "" )
; 1 )
)

Output: a text result of date values, in a return (¶) delimited list of FileMaker dates.

Examples: WorkDaysList ( "12/30/2005" ; "3" ; "1234567" ; "12/31/2005¶01/01/2006¶" )
12/30/2005¶
1/2/2006¶
1/3/2006¶
Parameters:

dateStart - Date, the date to begin the list of values.

numberOfDays - Positive or negative integer. numberOfDays facilitates determining when a
deadline or milestone will be, given a set lead/total work time allotted.

- If a positive integer is given (such as 10), the resulting list result will
contain 10 valid dates including the start date, going forwards.

- If a negative integer is given (such as -10), the resulting list result will
contain 10 valid dates including start date, going backwards.

daysOfWeek - Text block of numbers, 1-7, corresponding the the day of the week,
using the FileMaker day numbering method
( 1=Sun, 2=Mon, 3=Tue, 4=Wed, 5=Thu, 6=Fri, 7=Sat )
Blank or Empty daysOfWeek, will allow all days in the result,
same as entering "1234567".

Examples: "23456" = Week Days only
"17" = Weekends only
"2" = Mondays only etc....

excludeDatesList - A return (¶) delimited list of dates to exclude from the results.

-----------------------------------------------------------------------
Author - Jonathan Mickelson
Modified - 01-18-2012

 

Comments

Pablo D   Pablo D, Quito/Ecuador
Jun 18, 2013
Excellent, this is almost what I was looking for.
Is it possible to request this?
EndDate ( dateStart ; numberOfDays ; daysOfWeek ; excludeDatesList )
 
unix   unix, Japan
Jun 24, 2013
GetAsdate(RightValues(thisfunction;1))
 
Jonathan M.   Jonathan M., Los Anegeles
Jun 6, 2014
Just to be clear as unix said, you can just grab the last value...

the function also documents and has within it an simple variable preference that can be toggled to only return the end date:

displayResultDateOnly = 0 ; // Make this a 1 if you don't want the entire list of dates returned, just the end date.
 

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.