Newest functions Upload a function Leave a tip My account

There are 2,522 functions in this library.

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 (31 votes) Log in to vote

Jonathan Mickelson - Show more from this author

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

// SHORT CIRCUIT FOR NO VALID INPUT Case ( not IsEmpty ( dateStart ) and IsValid ( GetAsDate ( dateStart ) ) ; // CONTINUE FUNCTION Let ( [ excludeStartDate = 0 ; // Make this a 1 if you want to skip the start date, like when estimating a package arrival displayResultDateOnly = 0 ; // Make this a 1 if you don't want the entire list of dates returned, just the end date. xDatesList = excludeDatesList ; dStart = GetAsDate ( dateStart ) ; dOfW = Case ( not IsEmpty ( daysOfWeek ) ; GetAsNumber ( daysOfWeek ) ; "1234567" ) ; newDate = Case ( numberOfDays >= 0 ; dStart + excludeStartDate ; dStart - excludeStartDate ) ; newNumDays = Case ( numberOfDays >= 0 ; numberOfDays - 1 ; numberOfDays + 1 ) ; nextDate = Case ( numberOfDays >= 0 ; dStart + 1 ; dStart - 1 ) ; xDateExcludeList = Substitute ( ¶ & xDatesList & ¶ ; [ "¶0" ; "¶" ] ; [ "/0" ; "/" ] ) ; // THESE IMPROVE THE EXCLUSION DATE CHECKING xDateExcludeTestDate = Substitute ( ¶ & newDate & ¶ ; [ "¶0" ; "¶" ] ; [ "/0" ; "/" ] ) ; // THESE IMPROVE THE EXCLUSION DATE CHECKING var = "" ] ; Case ( // FAILED CRITERIA, MUST RECURSE AND DON'T WRITE ANYTHING. GetAsNumber ( Filter ( DayOfWeek ( newDate ) ; dOfW ) ) < 1 or ( PatternCount ( xDateExcludeList ; xDateExcludeTestDate ) > 0 ) ; WorkDaysList ( nextDate ; numberOfDays ; dOfW ; xDatesList ) ; // ALL DONE - WRITE LAST VALUE, NO MORE RECURSION newNumDays = 0 ; newDate ; // WRITE INTERMEDIATE DATES TO LIST, IF PREFERENCE IS TRUE Case ( displayResultDateOnly <> 1 ; newDate & ¶ ) // RECURSE AGAIN WITH NEW VAUES & WorkDaysList ( nextDate ; newNumDays ; dOfW ; xDatesList ) ) // END CASE ) // END LET ) // END CASE Click to copy

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¶" )
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

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!

 

Comments

		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, Japan Jun 24, 2013
GetAsdate(RightValues(thisfunction;1))

		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, 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 ( Date(1,25,2019); 2; "23456"; "11/28/2019" ) results in: 1/25/2019 1/29/2019

		mark woytovich, ignotum inc Jan 10, 2019
Edit (although the first works also) WorkDaysList ( Date(1,25,2019); 2; "23456"; Date(11,28,2019) )

		mark woytovich, ignotum inc Jan 10, 2019
woytovich@ignotuminc.com

		mark woytovich, ignotum inc Jan 10, 2019
woytovich@ignotuminc.com

		mark woytovich, ignotum inc Jan 10, 2019
woytovich@ignotuminc.com

		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, ignotum inc Jan 16, 2019
Thank YOU!

		shaib@gla-solutions.com, GLA Jun 22, 2019
Thank You Jonathan. Great CF. How do you convert the list into European Date format ? TIA Shai

		shaib@gla-solutions.com, GLA Jun 22, 2019
Thank You Jonathan. Great CF. How do you convert the list into European Date format ? TIA Shai

		shaib@gla-solutions.com, GLA Jun 22, 2019
Thank You Jonathan. Great CF. How do you convert the list into European Date format ? TIA Shai

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.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 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: