AnsweredAssumed Answered

Calculating Working Days and Holidays between Dates

Question asked by kayakjunkie on May 3, 2012
Latest reply on May 9, 2012 by kayakjunkie

Title

Calculating Working Days and Holidays between Dates

Post

I'm new to FMP and want to thank you in advance for any help.  I'm using FMPA 12 on Mac OSX 10.7.

I have a table with a list of dates indicating days when the stock market was closed.  I'm trying to determine the number of workdays between two dates taking into consideration weekends and holidays.

I've come across two solutions:

1)   http://help.filemaker.com/app/answers/detail/a_id/5281/related/1

This solutions uses a "counter" field to count the number of holidays in a year.  A lookup is performed on the HolidayList table returning the counter representing the holiday number (e.g. New Year's Day = 1, MLK Day = 2, President's Day = 3, etc.). This works well within a single calendar year but I don't see how to use this approach to cover scenarios where the date range extends across 2 or more years.

2)  http://www.fmfunctions.com/functions_display_record.php?functionId=261

This function uses a StartDate, EndDate and HolidayList as parameters.

/*
WorkingDays ( DateStart ; DateEnd ; HolidayList )
*/

Let (
$counter = $counter + ( Mod ( DateStart - 1  ; 7 ) < 5 and IsEmpty ( FilterValues ( DateStart ; HolidayList ) ) );
Case(
DateStart > DateEnd ; "" ;
DateStart = DateEnd ; $counter & Let( $counter = "" ; "" );
WorkingDays ( DateStart + 1 ; DateEnd ; HolidayList )
)
)
 
My table has DateStart and DateEnd fields that take user-input.  I have a separate table that contains a list of
stock market holidays from 1970 to the present.  When I input the formula above into the Custom Function editor
I'm given an error indicating that there is no "HolidayList" field.
 
I'm confused.  HolidayList is not a field.
 
Soooo, how does one create a function that will look at all the values in a list and count the number of times a date
included in the list falls between DateStart and DateEnd?
 
Thanks!
Jesse

 

 

Outcomes