Holidays

[nikster]

In a template that comes with V5.5 there is a file called Calculations

and Scripts. Within that file there is a calculation that calculates the

future end date of a project excluding weekends when given a number of work days. The calculation for the end date is as follows;

StartDate + Int (WorkDays/5) * 7 +

Middle ("12345012340123601256014560345623456",

(DayOfWeek (StartDate) - 1) * 5 +

Mod (WorkDays,5) + 1, 1)

This is working great for a Critical Path database I wrote with one exception. How do I get it to take into consideration holidays. If the calcualted date returns Dec 25 (Christmas) or January 1st (New Year's Day), how do I get it to move ahead a specified number of days and redo the calculation in case the specifed number of days falls again on a weekend.

Any help would be greatly aprrecited.

[Chuck]

You're going to get a VERY complex calculation with this. What you would do is define a field called PrelimEndDate with the calculation you posted. Then you could have another calculation that checked whether that date ended on any holidays, and if so, recomputed the end date by adding 1 to the start date. For instance, doing it for Christmas would be something like this:

Case(

[BobWeaver]

I think the simplest way to do this is to create a calendar file from which you can lookup the dates of sequential workdays. You also need a holiday file where you enter the holidays. Looking up data is where Filemaker really shines, so it's actually quite simple. In fact it was easier to create a sample file than write an explanation of it. Download the attachment to see how it works. The Holiday.fp5 file contains a list of holidays. In the Workdays.fp5 file is a list of all the workdays that cover the project period. You will need to generate this for the period that your project spans. enter the start and end dates and then click the Generate Calendar button. This will generate a list of dates excluding Saturday and Sunday and the holidays in the holiday file. Now you are all set. In the main Schedule.fp5 file are the calculations that generate the end date from the starting date and duration.

[LaRetta]

Bob,

This is also something I've been working on for our Employees db. I've established a calendar db in which the Agency enters holidays. I would love a copy of this. Is it available in Zip? Or is there a way I can somehow convert it? Thank you!

[BobWeaver]

Okay, I've attached a zip archive.

If you get it as a file called "download.php" just rename it to "Workdays.zip" and it should unzip. I've noticed this problem when downloading zip files, but it may just be my computer.

Workdays.zip

[LaRetta]

It downloaded fine! Thank you so much. I wish I had a Mac sitting at my desk also