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

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

Jonathan Mickelson   Jonathan Mickelson - Show more from this author

Share on Facebook Share on Twitter

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

  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):
WorkDaysList ( 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).
WorkDaysList ( 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 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¶" )

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

Change Log:
v1 - 01-18-2012 - JM - Modified for Misc Fixes & Format
v2 - 01-15-2019 - JM - Fixed error in matching Excluded Date Lists using Patterncount and leading 0's - Fixes false positives between 1 and 11 months & false negatives on formatting 01/01/2000 vs. 1/1/2000
* Thanks to Mark Woytovich for discovering the the Excluded Dates issue!



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
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.
mark woytovich   mark woytovich, ignotum inc
Jan 10, 2019
I am having an issue that maybe someone can help me with.
When I run this CF with a start date of 1/25/2019 and a duration of 2 days, using only 23456 days of the week and I include an exclude date of 11/28/2019 the result for the last day I get is 1/29/2019 instead of 1/28/2019.

WorkDaysList (


results in:

mark woytovich   mark woytovich, ignotum inc
Jan 10, 2019
Edit (although the first works also)

WorkDaysList (

mark woytovich   mark woytovich, ignotum inc
Jan 10, 2019
mark woytovich   mark woytovich, ignotum inc
Jan 10, 2019
mark woytovich   mark woytovich, ignotum inc
Jan 10, 2019
Jonathan Mickelson   Jonathan Mickelson
Jan 15, 2019
Thanks for finding that Mark! The function has been updated to fix a bad patterncount structure that was fincing 1/28 within 11/28 and being a false positive!
mark woytovich   mark woytovich, ignotum inc
Jan 16, 2019
Thank YOU!

Log in to post comments.


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

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 15 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth:

Under construction. Email me your wish list for improvements.