WorkingDays ( DateStart ; DateEnd ; HolidayList )
returns the number of working days between the two dates
Average rating: 4.2 (36 votes) Log in to vote
|
Daniele Raybaudi - Show more from this author
ACI http://www.aci.it |
Sample input:
WorkingDays ( "7/7/2010" ; "7/7/2010" ; HolidayList )
WorkingDays ( "7/3/2010" ; "7/4/2010" ; HolidayList )
WorkingDays ( "7/3/2010" ; "7/4/2010" ; HolidayList )
Sample output:
1
0
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., New York Jul 22, 2010 |
|
| Can someone explain how the statement $counter = $counter + (Mod ..... works? I can't follow it. | ||
|
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, 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, ACI Oct 29, 2010 |
|
| $counter isn't a field ! It is a local variable |
||
|
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, 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, ACI Nov 1, 2010 |
|
| Do you have an Advanced version of FileMaker ? | ||
|
Chris Coetzee, Cape Town Nov 2, 2010 |
|
| yes I do Pro 11 advanced | ||
|
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, 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, 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, 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, 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, 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, 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, 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, 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, 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, London Oct 9, 2016 |
|
| Hi all, Not sure how to make the holiday list be 'seen' by the function... Any ideas? Dom |
||
|
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. | ||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.