WorkDaysList ( dateStart ; numberOfDays ; daysOfWeek ; excludeDatesList )
Rate this function: Average rating: 4.7 (30 votes)
Discuss this Custom Function
Jonathan Mickelson, Thought Development Corp.
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.
|WorkDaysList ( "12/30/2005" ; "3" ; "1234567" ; "12/31/2005¶01/01/2006¶" )
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
- 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
- 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:
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):
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¶" )
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
Note: these functions are not guaranteed
or supported by BrianDunning.com. Please contact the individual
developer with any questions or problems.
This is my Custom Function and I want to
Make a comment about this Custom Function (please try to keep it brief & to the point). Anyone can post:
Newest Custom Functions:
||(Sat, Jun 16, 1:55pm)
||httpResponseCode ( responseHeaders )
||(Tue, Jun 12, 10:40pm)
||FindDuplicateCharacters ( string )
||(Fri, Jun 08, 5:01pm)
||FieldRepetitionLast ( field ; maxRepetition )
||(Wed, Jun 06, 6:05pm)
||CountModifiedRecords ( NameTimestampField ; StartTimestamp ; EndTimestamp )
||(Mon, May 28, 8:23am)
||JSONArrayLength ( JSONArrayStr )
||(Fri, May 25, 7:46am)
||interpolation (y1; y2; y3; x1; x2; x3; x1y1; x2y1; x1y2; x2y2; rnd)
||(Sat, May 19, 3:55am)
||@JSONFormatElements ( json )
||(Tue, May 15, 3:31am)