8 Replies Latest reply on May 9, 2012 8:36 AM by kayakjunkie

    Calculating Working Days and Holidays between Dates

    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

       

       

        • 1. Re: Calculating Working Days and Holidays between Dates
          philmodjunk

          HolidayList is a parameter as is DateStart and DateEnd.

          Make sure all three are defined as parameters and check the spelling to make sure what you declared as a parameter is exactly what you are using in the functions defined calculation.

          • 2. Re: Calculating Working Days and Holidays between Dates
            kayakjunkie

            HolidayList is included as a Parameter.

            I took some screen shots, but I don't know how to get them into a post. I've tried uploading, but am not sure if only one image will upload.  Anyway,

            My Preferences Table includes the following Fields

            startDate

            endDate

            WorkDays

            My Holidays Table includes

            Date

            Holiday

             

            the field WorkDays is a calculation field as follows:

            Unstored, = WorkingDays ( startDate ; endDate ; Holidays::Date )

             

            Using 01/01/12 as the startDate and 02/29/12 as the endDate the calculation returns 43 as the answer.  Taking into consideration New Year's, MLK and President's Days, the correct answer is 40.

            • 3. Re: Calculating Working Days and Holidays between Dates
              philmodjunk

              The issue would appear to be in the custom function definition--which is where you define the parameters to be used in the function.

              The parameters are circled in red in the screen shot I am uploading. (To upload more than one screen shot with the same message post, use an image editing program to combine the images.)

              • 4. Re: Calculating Working Days and Holidays between Dates
                kayakjunkie

                Thanks for letting me know how to upload multiple screen shots.

                The parameters appear to have been entered properly.  Does the formula itself make sense?  

                Any thoughts?

                 

                Jesse

                • 5. Re: Calculating Working Days and Holidays between Dates
                  philmodjunk

                  I see "HolidayList" has become "Holidays".

                  Does this mean that you are no longer getting that error message?

                  Holidays needs to be a return separated list of of all Holiday dates.

                  What I see in your original post is:

                  WorkingDays ( StartDate ; EndDate ; Holidays::Date )

                  That only passes a single date to the function instead of a list of holiday dates.

                  If you have this relationship:

                  Portfolio::anyfield X Holidays::anyfield

                  Change the function call to:

                  WorkingDays ( StartDate ; EndDate ; List ( Holidays::Date ) )

                  and this will pass a list of all Holiday Dates in the Holidays table to the function.

                  • 6. Re: Calculating Working Days and Holidays between Dates
                    kayakjunkie

                    I'm not receiving any errors now when I exit the Custom Function editor.  However, the function still isn't working.

                    I've changed the function call to add the List (Holidays::"Date) as the third parameter.  I've also tried changing the CF definition by changing

                    IsEmpty ( FilterValues (StartDate ; Holidays )
                    to
                    Is Empty (FilterValues ( List ( Holidays::Date ))
                    However, when I attempted to exit the CF editor, I received an error message indicating that it was expecting a Paren at the first colon in
                    Holidays::Date.
                     
                    Let ( 
                    
                    $counter = $counter + ( Mod ( StartDate - 1  ; 7 ) < 5 and IsEmpty ( FilterValues ( StartDate ; 
                    List ( Holidays::Date ) ) ) ); Case( StartDate > EndDate ; "" ; StartDate = EndDate ; $counter & Let( $counter = "" ; "" ); WorkingDays  ( StartDate + 1 ; EndDate ; Holidays ) ) )
                     
                    I've also tried changing the Date field in Holidays from "Date" to "Text" and back to "Date" without any effect.
                     
                    As the Layout image shows, these changes (with and without the change in the CF defintion) does not make a change in the results.
                    It still is not picking up the number of holidays, which in this case should be 3.
                     
                    Is the relationship set-up correctly?  Any other suggestions?
                    • 7. Re: Calculating Working Days and Holidays between Dates
                      philmodjunk

                      The relationship between preferences and holidays should be either:

                      Preferences::anyField X Holidays::anyfield

                      or

                      Preferences::Startdate < Holidays::date AND
                      Preferences::Enddate > Holidays::date

                      Note that with the second relationship, you don't actually need the custom function to count the number of holidays.

                      Count ( Holidays::date )

                      Will return the number of holiday dates from StartDate to EndDate for that Preferences record.

                      That can be used in combination with this method: http://help.filemaker.com/app/answers/detail/a_id/5281/kw/%22number%20of%20days%22

                      To calculate the numer of elapsed days.

                      • 8. Re: Calculating Working Days and Holidays between Dates
                        kayakjunkie

                        Wow!  That solved the problem.  I've gone round-and-round with the calculations and scripts trying to solve the problem, even used the

                        count ( holiday::dates ) at one point.

                         

                        It turns out the key was getting the relationship correct.  I didn't know about the operators available in making the link.  Showing the <= and >=

                        operators in your example above led me to explore the relationship link further, voila, it works!

                         

                        Thank you sooooo much for your help (and patience)!

                         

                        Jesse