9 Replies Latest reply on Aug 15, 2012 7:44 AM by philmodjunk

    Business day counter

    Matty_1

      Title

      Business day counter

      Post

      Hello,

       

      I'm wondering if someone could help me come up with a formula that can count 15 business days (business days being Monday-Friday in this case) after a particular date.

      Thank you!

        • 2. Re: Business day counter
          Matty_1

          Thank you but this does not suffice, I need a calculation that starts on say August 2nd 2012 and counts 15 days business days ahead.  If I have to calculate the end date then that defeats it.

          • 3. Re: Business day counter
            philmodjunk

            What about holidays? If there's a major holiday in that 15 day interval does that day also count as part of the 15 or should it be skipped over as well?

            • 4. Re: Business day counter
              Matty_1

              For now our payments policy does not take into account holidays.  If it's at all possible to have both then that would be greatly appreciate.

              • 5. Re: Business day counter
                philmodjunk

                The approach is markedly different when you include holidays. We have a law where certain customers have to wait three business days before we pay them for nonferrous scrap metal.

                weekend dates can be calculated with a simple calculation. Days closed due to holidays constantly changes. We solved this by setting up a server schedule controlled script that keeps a table of records with one record for each day that we are open--that gives us our standard 6 days open. Then we use a Calendar interface to record additional days that we are closed.

                We can then use a relationship that matches only to records in this table that are "open" and have dates greater than or equal to today's date to determine the desired future date. GetNthRecord ( DaysOpen::Date ; 16 ) would then return a date 15 business days in the future. (Does not count today as one of the 15.)

                • 6. Re: Business day counter
                  Matty_1

                  Ok thank you Phil, what exactly does the scheduled script do, create records N number of days after the current day??

                   

                  Also do you have a solution that doesn't involve keeping track of major holidays and that simply counts 15 days ahead of any given day (monday-Friday being the business days)??

                   

                  Thank you in advance.

                  • 7. Re: Business day counter
                    philmodjunk

                    The schedule script adds new records each night such that there are records in the table something like a month and a half in advance of today's date. I don't have a calc for just weekdays readily available. You might search one of the custom function web sites for one. Even if you do not have fileMaker advanced, you may be able to adapt the calculation used in the custom function for use in a calculation field.

                    • 8. Re: Business day counter
                      Matty_1

                      Hello Phil, so I have the above suggestion working properly and I'm wondering if you could also show me how to calculate 15 weekdays ahead of a praticular date without worrying about holidays?  I'm assuking there's a method where you can achieve the same result without having to use GetNthRecord, correct?

                      Thanks!

                      • 9. Re: Business day counter
                        philmodjunk

                        I'm sure that it's possible, but I haven't had the time to take the method from the knowledgeBase article and produce a version where output and input is switched. (Specify the interval to get the date instead of specifying the date to get the interval.