I have been struggling with this for a little while now. I am a self-taught intermediate user.

I have a database with related tables where one of the functions is to calculate a start date based on the end date. The other parameters variables relating to the size of the project that are stored in a separate table. I have been successful in Calculating the start date, based on a five day work week, but have not been able to set up a calculation to exclude statutory holidays and to ensure that the start date is not a weekend or a stat holiday.

I have a main project table that among other things contains the user-entered variables used in the calculation. I also have a table that contains other variables that are not user-modifiable. I also have a table where the date calculation function is done. Finallu I have set up a table that accounts for years of dates and indicates if a work day, holiday or weekend day. What I want to do is look-up or find a set of records that fall in between the calculated start date (that does include holidays) and the entered end date; then use a summary function to count the number of stat holidays in the range; then add that number to the task days I have already calculated to come up with a start date that reflects holidays.

I have tried using a script, but cannot seem to get the Find step to include the variable (based on the current mainrecord) dates. I have also fumbled with a portal with no luck.

Any suggestions???

You might be able to use one of these CFs:

http://www.briandunning.com/cf/798
http://www.briandunning.com/cf/760

Or, starting from the difference in workdays:

http://www.briandunning.com/cf/353

craft your own algorithm to adjust for the holidays (a recursive CF or script to count the number of holidays in the range is not too hard).

Custom functions require FileMaker Pro Advanced to install. If you don't have Advanced, you may still be able to use the function's calculation, as long as it's not recursive. For recursive CFs, you can use the basic algorithm, but in a script instead.

Thanks for the suggestions -- I don't have Advanced, but I will try using the shipby function you referred to, but I don't really know where to start -- do I insert the function calculation within a script?

I already have a table of holidays that has a boolean field for whether or not a date is a holiday, and another for whether or not it is a weekend. My plan of action had been to do a Find based on the entered finish date and a calculated estimated start date (these are in a separate table), then count the number of holidays and weekend days and add those to the estimated start date to come up with a start date that would not be on a weekend or holiday. But, I am stuck on how to indicate a range of dates in a set of Find criteria within a script. Seems simple, but I can't seem to make it happen.

I have about 500 records in the current database that need to be reviewed to ensure they aren't scheduled to start on a weekend or holiday.

If the problem is to find records that might have an invalid Start Date, define a calc that compares the calculated Start Date (calculated based on one of those algorithms) with your Start Date, and flags them. Then Find for records that have this flag indicated.

But it would be simpler to just replace your Start Date with a correctly calculated Start Date, using one of those algorithms in a Replace Field Contents (be sure to back up your file before attempting a Replace, as there's no Undo) if it must be a regular Date field to be editable, or define it as a Calculation with Date result otherwise.

The general idea of those algorithms, is you provide them with an initial date, the number of days to offset, and a return-separated list of holidays, and it provides a resulting date. To fit them into a calc without FM Advanced, you use fields or variables in place of the listed parameters.

Thanks for the suggestions, I was able to solve the problem. I made a new relationship between the two tables that allowed the portal to show dates before a user entered finish date and after a calculated start date (one that did'nt account for holidays).

I guess I had forgotten, or not realised, that a relationship could be other than "=". In this case I minded my dyslexia and made certain my "≥" and "≤" were pointing in the right direction. It also helped to make a separate instance for the table to ensure the relationship would work.

The portal also shows fields that indicate whether or not dates are holidays. I included a summary count of the holidays in the portal set, then used that count in a further calculation to come up with a date that accounted for statutory holidays. Finally I made sure that date didn't start on a weekend or a statutory holiday.

I suppose my solution is not elegant as it has a number of steps, but to me it is logical and I can track the results throughout the calculation. This helped me understand what I was doing and figure out where I had errors.