Brian Dunning's FileMaker Custom Functions

WorkingDays ( DateStart ; DateEnd ; HolidayList )

returns the number of working days between the two dates

  Average rating: 4.2 (35 votes) Log in to vote

Daniele Raybaudi   Daniele Raybaudi
ACI
http://www.aci.it

Share on Facebook Share on Twitter

  Sample input:
WorkingDays ( "7/7/2010" ; "7/7/2010" ; HolidayList )
WorkingDays ( "7/3/2010" ; "7/4/2010" ; HolidayList )
  Sample output:
1
0

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

returns the number of working days between the two dates.
( No weekend days nor any day of the HolidayList )

 

Comments

Rich Y.   Rich Y., New York
Jul 22, 2010
Can someone explain how the statement $counter = $counter + (Mod ..... works? I can't follow it.
 
Daniele Raybaudi   Daniele Raybaudi, ACI
Jul 22, 2010
$counter, that will be the result of the Custom Function, starts from 0 and add 1 to itself
IF:
the day isn't Saturday or Sunday [ Mod ( the date - 1 ; 7 ) MUST be < 5 ]
AND
the day isn't in the List of Holidays
 
Chris Coetzee   Chris Coetzee, Cape Town
Oct 29, 2010
I still dont understand the $counter field.
If I try to create it as a field the "$" prevents me from doing so and what will the calculation be ?Sorry...I just dont get it .
Thanks
 
Daniele Raybaudi   Daniele Raybaudi, ACI
Oct 29, 2010
$counter isn't a field !

It is a local variable
 
Chris Coetzee   Chris Coetzee, Cape Town
Oct 29, 2010
Thanks for your reply.I now understand that it is a local variable which the $ donates.
What does Counter represent and how do I intergrate it into the calculation ?
 
Chris Coetzee   Chris Coetzee, Cape Town
Nov 1, 2010
Hi..I would like to use this calculation.Woudl appreciate it if you would point me in the right direction with regard to the "Counter" variable
 
Daniele Raybaudi   Daniele Raybaudi, ACI
Nov 1, 2010
Do you have an Advanced version of FileMaker ?
 
Chris Coetzee   Chris Coetzee, Cape Town
Nov 2, 2010
yes I do Pro 11 advanced
 
Daniele Raybaudi   Daniele Raybaudi, ACI
Nov 2, 2010
So why worry about the name of a variable contained within the CF.
Copy the entire calculation and enter it directly in the space reserved for the calculation of the CF.
Since this function is recursive, it can not be used in a standard calculated field.
 
Robert Trepanier   Robert Trepanier, Lebanon, NH
Nov 8, 2010
Buon giorno,
Thank you for your reply to my post @ FM forums and "sorry" for the dialog you needed to endure from others. I may be over-thinking this CF and could use more advice.
A) I'm not able to veiw the results of the calculation. Do I create a new field to point to the CF?
B) Is the holiday list to be defined in a value list?
Thanks much,
Bob
 
Daniele Raybaudi   Daniele Raybaudi, ACI
Nov 8, 2010
Robert, send me an email at:
raybaudi(at)libero(dot)it.

I'll return to you a working example.
 
Brian Rich   Brian Rich, Hamble, UK
Feb 10, 2011
This function does not appear to evaluate consistently or correctly.

WorkingDays ( "03/01/2010"; "06/11/2010"; "") does not produce a result at all.

If I use date fields instead eg:

WorkingDays ( dateFirst;datelast;"")

a value is returned but it returns different answers for the same pair of dates, usually 2 days different. For examle, if the date fields contain the values 03/01/2010 and 06/11/2010 respectively sometimes it returns 75 days, sometimes 77!

I'm using FMP11 Advanced
 
Vaughan   Vaughan, Sydney AUstralia
Oct 21, 2012
"03/01/2010" is not a date, which is why the cf fails.

If you must enter a fixed string, use the Date() function:

Date( month ; day ; year )

Note that "03/01/2010" could either be 3 January 2010 or 1 March 2010 depending on how dates are displayed in your region.
 
Vaughan   Vaughan, Sydney AUstralia
Oct 23, 2012
Hi Daniele, just wanted to let you know I've found another use for this cf.

If you enter the same date as the start and end date, the cf returns 1 if it's a work date and 0 if it's a weekend or holiday.

Thanks.
 
Jack   Jack, Lebanon
Feb 28, 2014
Hi Daniele am new to filemaker please can you explain what do u mean by holiday list, is it a value list ,table or field
thanks
 
Daniele Raybaudi   Daniele Raybaudi, ACI
Mar 3, 2014
"HolidayList" can be:
a text, a text field, a calculation field with text result, a variable, whatever you wish to use but containing a list of dates.
Practically it is better to create an holidays table, each record representing an holiday, then a value list getting its values from that table.
 
Bob Barker   Bob Barker, ThePriceIsRight
Oct 6, 2016
I used this function and it worked perfectly! Thanks a bunch!

For those who it is not working for, be sure you are passing the HolidayList parameter as a list. I have this function called in a calculation field and it looks like:

WorkingDays( GetAsDate(Date Initial) ; GetAsDate(Date Final); List(Holidays::Date)
 
Bob Barker   Bob Barker, ThePriceIsRight
Oct 6, 2016
How is this a recursive function? Is it because it calls itself at the end with: WorkingDays ( DateStart + 1 ; DateEnd ; HolidayList )??
 
Dom Moir   Dom Moir, London
Oct 9, 2016
Hi all,

Not sure how to make the holiday list be 'seen' by the function...

Any ideas?

Dom
 
Fred   Fred, CA
Oct 11, 2017
The function seems to have a delay as you scroll through records and it also seems to not work correctly each time (or is it my FileMaker?). In a field I will get a long string of numbers, but if I click in the field, it will suddenly change and display the number of days correctly.
 

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.