8 Replies Latest reply on Jan 14, 2017 1:35 PM by philmodjunk

    How to caculate an EndDate when given a Startdate and a Duration, excluding Holidays and Weekends?

    jda76

      Title

      How to caculate an EndDate when given a Startdate and a Duration, excluding Holidays and Weekends?

      Post

      I have found a posting on here about having an EndDate calculate based on duration, omiting the weekends (which works great), but I would like to also omit any holidays that fall within the duration. 

       

      Ideally, the holidays would be in another table and added to as needed.

       

      Thanks for any help! 

        • 1. Re: How to caculate an EndDate when given a Startdate and a Duration, excluding Holidays and Weekends?
          TSGal

          jsmarvey:

           

          Thank you for your post.

           

          In your table of holidays, have an auto-enter serial number entered for each holiday date.  That way, when the information is looked up for beginning date and ending date, we can see the difference between the two holiday dates.  For example, suppose you have the following USA Holiday dates:

           

          Auto-Enter Number - Date - Description 

          1 - 1/1/2008 - New Year's Day

          2 - 5/30/2008 - Memorial Day

          3 - 7/4/2008 - Independence Day

          4 - 9/1/2008 - Labor Day

          5 - 11/28/2008 - Thanksgiving Day

          6 - 11/29/2008 - Day after Thanksgiving

          7 - 12/25/2008 - Christmas

          8 - 12/31/2008 - New Year's Eve

           

          If the beginning date is 7/1/2008 and the duration is 10 business days, the result is 7/15/2008.  If we use a lookup for the beginning date and see that the next lower value is 5/30/2008, the auto-enter number is "2".  If we use a lookup for the ending date, the next lower value is 7/4/2008 and the auto-enter number is "3".  That means 3-2 or 1 holiday has occurred during the time frame, so we need to add one more business day.  Does this make sense?

           

          If you need further clarification or have any additional questions, please let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: How to caculate an EndDate when given a Startdate and a Duration, excluding Holidays and Weekends?
            jsalzer_1
              

            Careful, there.

             

            That suggestion has two possible flaws.  First, it assumes that the user is going to enter holidays into the holidays table in chronological order.  This may not always happen (say you normally have Thanksgiving off and - in a generous mood - the boss tells you last minute that the Friday after is also going to be a holiday).

             

            Second, if the number of days added to the end ends up encompassing a new holiday (or landing on a holiday as the end date), then there's a problem.

             

            I have no solution as of yet, but I wanted to point out these two trouble spots that a solution would have to consider. 

            • 3. Re: How to caculate an EndDate when given a Startdate and a Duration, excluding Holidays and Weekends?
              jda76
                

              Thanks TSGal!

               

              Although jsalzer's cautions are valid, I think that this is a great start for me.

               

              Ultimately the additional business days would be added to the startdate and then I would proceed with the "omit weekends" part of the calculation, right?

               

              I understand the logic as you have outlined, except for how to construct the lookups which will provide me the auto-enter number from the Holidays table.

               

              Here is how I have envisioned setting this up:

              tables: Schedules, SchedLines, Holidays

              From a parent record in Schedules, we will add lines which will record dates and durations of passes

              of project materials between my company and our clients. 

              The Startdate on line1 will have to be manually entered or possibly lookedup from the parent record's "project startdate".  Then the duration of workdays will be manually entered and the Enddate would be calculated (omitting weekends and any dates from the Holidays table).

              The Startdate on line2 can be a lookup showing the Enddate from line1, and only the duration will need to be manually entered, and the Enddate, again, will be calculated.

              The rest of the schedule will continue accordingly for as many passes as necessary.

               

              I assume the lookup field will be in SchedLines but I'm not clear what the relationship to Holidays would be in order to pull that data.

               

              I appreciate all your help!  This forum rocks! 

              • 4. Re: How to caculate an EndDate when given a Startdate and a Duration, excluding Holidays and Weekends?
                TSGal

                jsmarvey:

                 

                Yes, you will use the "omit weekends" part of the calculation first.

                 

                The lookup values I outlined previously were actually taken from FileMaker Pro 6 and earlier.  For FileMaker Pro 7 and later, this is accomplished a little differently, but still following the same logic.

                 

                In your Holidays file, you will not need an Auto-enter number field.  As long as you have the holiday dates, you should be fine.

                 

                Pull down the File menu and select "Manage -> Database".  Click on the Relationships tab and find your Holidays table and Schedules table.  You are first going to draw a line from your Startdate field to the Date field in your Holiday table.  In addition, draw a line from your Enddate calculation field to the same Date field in your Holiday table.  Between the two tables, you will see an icon.  If you double-click the icon, an "Edit Relationship" dialog box appears.  In the middle section should show something like:

                 

                Startdate = Holiday Date

                AND Enddate = Holiday Date

                 

                In the upper section, Startdate is probably highlighted, and in the middle, there is a drop down box showing an equal sign.  Change this to ≤ (less than or equal to) and click "Change".  You will notice in the middle section that this has changed.

                 

                Next, highlight the second criteria in the middle section, and at the top of the screen, you should now have Enddate highlighted.  Again, we are going to change the equal sign, except this time, change this to ≥ (greater than or equal to) and click "Change".  In the middle section, you should now see:

                 

                Startdate ≤ Holiday Date

                AND Enddate ≥ Holiday Date

                 

                This looks for all records where the Enddate crosses a Holiday date.

                 

                Click "OK" to leave this dialog box, and click "OK" again to return to your layout.

                 

                In your Schedule table, pull down the View menu and select "Layout Mode".  On the right side of the screen, click the tool just below the oval tool.  This is the portal tool.  Draw a portal on an empty area of your screen.  Be sure to use the portal to view records from your Holiday table, and for this example, include the Holiday Date field.

                 

                Pull down the View menu and select "Browse Mode", so we can enter a test record.

                 

                Assuming you have Independence Day in the Holiday Table, enter a Startdate of 6/30/2008, and give it a duration of 30 days.  Enddate will calculate sometime in August, and in the portal, 7/4/2008 should appear.

                 

                Create a new calculation field, DAYS EXTRA, with the formula:

                 

                Count (Holidays::Holiday Date)

                 

                This will count the number of records in the portal.  If StartDate and Enddate extend over a holiday like the example above, then the result is 1.  If there are more than one holiday (over Christmas season), the answer could be 2 or more.  If no holidays happened, the result would be 0 (zero).  In any case, this number could then be added again to the days result, and if the days added occurs over a weekend, this would need to be taken into account once again.

                 

                I know there is a lot of information here, so take it slowly.  If I wasn't clear in any of the above steps, please let me know.

                 

                TSGal

                FileMaker, Inc.

                • 5. Re: How to caculate an EndDate when given a Startdate and a Duration, excluding Holidays and Weekends?
                  davidhead
                    

                  I have been playing around with a solution to this.

                   

                  TSGal, I am assuming that your solution involves either a recursive custom function or a looping script?

                   

                  Because once you find the first end date, you need to check for holidays between the start and end, then add days, then check for ending on a weekend, then add days, then check for more holidays, then add days... It gets complicated :)

                   

                  If you get a solution working, I would be interested in seeing a sample file. 

                  • 6. Re: How to caculate an EndDate when given a Startdate and a Duration, excluding Holidays and Weekends?
                    TSGal

                    David:

                     

                    Although I could use a recursive custom function and/or looping script, I'm doing this with straight calculation fields.

                     

                    Granted, this is not a perfect solution, but for the most part, it should help "jsmarvey" (the customer).

                     

                    This is what I have to this point:

                     

                    The customer has the following fields:

                     

                    Startdate (Date)

                    Duration (Number)

                    Enddate (Calculation:Date) = Startdate + Duration

                     

                    The customer wants to remove weekends, so let's make sure the Startdate is not a weekend.  Therefore, create a new calculation field, "Weekday Startdate", with the following formula:

                     

                    Startdate + Middle ("1000002"; DayOfWeek (Startdate); 1)

                     

                    That is, we take the Startdate, and if it occurs on a Sunday, we add one day.  If it occurs on a Saturday, we add two days.  All other days, we leave the same (add zero days).

                     

                    For every five days, we add a week.  For the remainder, it can be a bit tricky, so let's look at the following table:

                     

                                M - Tu - W - Th - F

                    0 days = 0 - 0 - 0 - 0 - 0

                    1 day  = 1 - 1 - 1 - 1 - 3 

                    2 days = 2 - 2 - 2 - 4 - 4 

                    3 days = 3 - 3 - 5 - 5 - 5

                    4 days = 4 - 6 - 6 - 6 - 6

                     

                    If the remainder is 0 days, then zero days are added.  If the remainder is 1 day, we add one day, except on Friday, we add three days (Friday to Saturday to Sunday to Monday).  If the remainder is 4 days, we add four days to Monday (that brings us to Friday), while we add 6 days to the other days to get around the weekend.  The other days follow the same pattern.

                     

                    We'll put this into an string array to get Enddate.

                     

                    Weekday Startdate + Int (Duration/5) * 7 +

                       Middle ("0000011113222443355546666"; Mod (Duration; 5) * 5 + DayOfWeek (Weekday Startdate) - 1; 1)

                     

                    That is, we take the Weekday Startdate, add the multiple number of weeks, and then with the remainder, we use the Mod function to get the remainder, multiply it by 5 to get us to the starting days value in the array string, take the DayOfWeek and subtract 1 to get the correct starting point.

                     

                    This gives us the result without taking into account any holidays.  Assuming in the Holidays table we have the Holiday Date field, and the relationship was created above in an earlier post, we can create a "HolidaysEnddate" field.

                     

                    Enddate + Count (Holidays::Holiday Date)

                    ....seems logical, but again, we need to take into account the weekend.  Therefore, the calculation would be:

                     

                    Enddate +

                       Middle ("0000011113222443355546666"; Mod (Count (Holidays::Holiday Date), 5) * 5 + DayOfWeek (Enddate) - 1; 1) 

                     

                     

                    Yes, there is a possibility that the extra days may cross over another holiday, but this will work for a large majority of cases.

                     

                    TSGal

                    FileMaker, Inc. 

                    • 7. Re: How to caculate an EndDate when given a Startdate and a Duration, excluding Holidays and Weekends?
                      u177svr

                      Hi Teams,

                      I saw you're answer from a few years ago and found this very interesting, but it did totally bamboozle me as I'm new to FMpro, and am learning things as I go along.

                      Please help me understand this as this would really help with my Turnaround Times by excluding weekends and Holidays. I've also never done relationships before so can you explain in simple terms what I need to do.

                      To explain what I have at present:

                      Field Name:                                       Field Type:

                      Date Product Arrived                            Date

                      Date Order Arrived                               Date

                      Date of Shipping Order                        Date

                      TAT (Turnaround Time) for Order        Date

                      Turnaround Time                                 Calculation                =Date of Shipping Order - TAT for Order

                       

                      So at present my TAT for Order field chooses the nearest marker between Date Product Arrived and Date Order Arrived as my TAT starts when I receive both ( I could have the product in stock before the order comes through and vice versa) This gives the result in Turnaround Time Field, but I want to exclude Bank Holidays and Weekends.

                      • 8. Re: How to caculate an EndDate when given a Startdate and a Duration, excluding Holidays and Weekends?
                        philmodjunk

                        I suggest a simpler solution. Set up a table either of all days in the calendar or all non-holiday work days. With only work days in the table, you can set up a relationship like this:

                         

                        YourTable::StartDate <= WorkDays::WorkDate

                         

                        then this calculation can be used to produce the desired date:

                         

                        GetValue ( List ( Workdays::WorkDate ) ; YourTable::Duration )

                         

                        If your table includes weekends and holidays, you can add another pair of match fields to filter out non-work days.

                         

                        Another option possible with today's FileMaket is to use the ExecuteSQL ( ) function to get the desired date. From this table.