3 Replies Latest reply on Jan 10, 2015 11:04 AM by philmodjunk

    Calculating a Finish Date Given a Starting Date and the Number of Work Days

    StuartDuggan

      Title

      Calculating a Finish Date Given a Starting Date and the Number of Work Days

      Post

      Hi, Can someone please help?

      I am trying to determine an 'EndDate' that is 10 weekdays after a 'StartDate'. In addition to Saturdays and Sundays, I want to exclude known weekday holidays, which i have included in lookup tables. I have proved that the 'LookUp_High' and 'LookUp_Low' values are correct, however the calc is still not returning the correct date and provides weekend dates?

      The following is where i'm up to at present:

      StartDate + Int ( 10 / 5 ) * 7 + Middle ( "12345012340123601256014560345623456" ; ( DayOfWeek (StartDate) - 1 ) * 5 +  Mod (10 ; 5 ) + 1 ; 1 )- (Lookup_High - Lookup_Low)

       

      In anticipation

      Stuart

        • 1. Re: Calculating a Finish Date Given a Starting Date and the Number of Work Days
          philmodjunk

          I recognize this function from a KnowledgeBase article where I once helped FileMaker fix a typo in the function.

          But what about non weekend holidays? Will you need to adjust this count by allowing for closures on days other than Sat, Sun?

          I ask that, because there is a completely different method that I've used that allows for closures on any day of the week--both regular and holiday closures.

          • 2. Re: Calculating a Finish Date Given a Starting Date and the Number of Work Days
            StuartDuggan

            Hi Phil,

            Many thanks for your reply.

            I want the EndDate to be a weekday (Mon, Tue, Wed, Thu, Fri); however, i don't want to include known weekday bank holidays. i.e if the 10th day after my StartDate is on Easter Monday, i want the EndDate to be the following day (Tuesday).

            Best Regards

             

            Stuart

            • 3. Re: Calculating a Finish Date Given a Starting Date and the Number of Work Days
              philmodjunk

              I will read that as "yes, I do need to account for holiday closures in addition to week end dates". wink

              I worked on a DB where the State of California required a 3 day "hold" on certain transactions. We had to hold the customer's money for at least 3 business days before they returned to get their money from us. What I set up was a "calendar" table where I had one record for each day of the year and "marked" which records were for dates that we were open. I had a way to flag specific days of the week (we were open on Saturdays, closed on Sundays) as "always closed". I could pull up a calendar display of these dates and click a button on the days that represented a holiday closure.

              I then set up a relationship similar to this:

              MainTable::Date < Calendar::Date AND
              constOpen = Calendar::Status

              ConstOpen was a calculation field that returned the same value we set in the status field of Calendar to mark a record as a day that we were "Open".

              Then we used this calculation to return a date that was 3 days into the future:

              GetNthRecord ( Calendar::Date ; 3 )

              In your case, you would put the field specifying the number of business days in place of the 3 that we needed.

              We used a server scheduled script to keep adding new day records to the calendar table (we only needed a few weeks "lead" on the current date) And we had a "calendar layout" where we could use mouse clicks to control what days were marked as "open". That calendar layout was set up similar to this calendar demo file: https://www.dropbox.com/s/e8d03xvwe8vtz85/Calendar.fp7

              This file is in the older file format so you will probably need to use Open from FileMaker's File menu to open the file and produce a copy converted to the .fmp12 file format.