
1. Re: How to caculate an EndDate when given a Startdate and a Duration, excluding Holidays and Weekends?
TSGal Nov 17, 2008 12:53 PM (in response to jda76)jsmarvey:
Thank you for your post.
In your table of holidays, have an autoenter 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:
AutoEnter 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 autoenter number is "2". If we use a lookup for the ending date, the next lower value is 7/4/2008 and the autoenter number is "3". That means 32 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 Nov 17, 2008 4:43 PM (in response to jda76)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 Nov 18, 2008 3:04 PM (in response to 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 autoenter 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 Nov 19, 2008 7:53 AM (in response to jda76)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 Autoenter 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 doubleclick 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 Nov 19, 2008 8:15 AM (in response to jda76)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 Nov 19, 2008 10:28 AM (in response to jda76)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 Jan 14, 2017 8:12 AM (in response to jda76)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 Jan 14, 2017 1:35 PM (in response to jda76)I suggest a simpler solution. Set up a table either of all days in the calendar or all nonholiday 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 nonwork days.
Another option possible with today's FileMaket is to use the ExecuteSQL ( ) function to get the desired date. From this table.