Software Venture Consulting

FileMaker Pro downloads & Resources
FileMaker Custom Functions
FileMaker Web Viewer Examples
FileMaker Pro & Lasso Consulting
Training
FileMaker Books
FileMaker Articles
FileMaker Error Reference

Free Web Tools
Free FileMaker Tools

Personal Pages
Videos
Adventures
Links

Shopping Cart
Shopping Cart

Search:

Free Newsletter
Signup


Contact


Privacy Policy



FileMaker is a registered trademark of FileMaker, Inc. in the U.S. and other countries.

 

 FileMaker Pro Custom Functions

List  |  Show Random  |  Upload  |  Add This to Your Site

WorkDaysList ( dateStart ; numberOfDays ; daysOfWeek ; excludeDatesList )

Rate this function:  

RatingRatingRatingRatingRating
  Average rating: 4.7  (30 votes)
  Discuss this Custom Function

Jonathan Mickelson, Thought Development Corp.
http://www.thought-dev.com

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.

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¶


 Then copy & paste into FileMaker Advanced's Edit Custom Function window.

Click here to copy To Clip Manager if you have myFMbutler's Clip Manager installed

Description:

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

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 edit it

Discuss:

Excellent, this is almost what I was looking for.
Is it possible to request this?
EndDate ( dateStart ; numberOfDays ; daysOfWeek ; excludeDatesList )

Pablo D, Quito/Ecuador
June 18, 2013 11:26am

GetAsdate(RightValues(thisfunction;1))

unix, Japan
June 24, 2013 9:09pm

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.

Jonathan M., Los Anegeles
June 06, 2014 2:25pm

Make a comment about this Custom Function (please try to keep it brief & to the point). Anyone can post:

Your Name:
City/Location:
Comment:
characters left. If you paste in more than 1500 characters, it will be truncated. Discuss the function - advertisements and other useless posts will be deleted.
Answer 0 + 3 =
Search for Custom Functions:

Custom Functions Widget
Download the Custom Function Dashboard Widget for OS X
Keep all the latest Custom Functions right at your fingertips!

Newest Custom Functions:

1. decFromHex ( hexValue )
  (Mon, Oct 16, 12:34pm)
2. MonthYearList ( startmonth ; startyear ; numbermonth ; short )
  (Mon, Oct 16, 3:38am)
3. ErrorDescription ( errorNumber )
  (Wed, Sep 27, 2:51am)
4. decodeEntities (text)
  (Wed, Sep 27, 1:22am)
5. HexidecimalToNumber ( HexidecimalValue ; counter )
  (Mon, Sep 25, 12:39pm)
6. FixedFieldConverter ( Fieldname ; f1 ; f2 ; f3 ; f4 ; f5 ; f6 ; f7 ; f8 ; f9 ; f10 ; f11 ; f12 ; f13 ; f14 ; f15 ; f16 ; f17 ; f18 ; f1
  (Fri, Sep 15, 12:34pm)
7. ShannonEntropy ( text )
  (Thu, Sep 07, 5:59am)
8. LetterCount ( text ; summary )
  (Thu, Sep 07, 5:53am)

RSS Feed of Custom Functions