Working Day Calculations

[BobWeaver]

Since this issue comes up regularly...

Here is a set of sample files that allows you to do date and duration calculations accounting for non-working days (weekends and holidays).

There are 3 files:

Schedule.fp5 is the main file which does the example calculations.

Holidays.fp5 contains a user-entered list of holidays.

Workdays.fp5 contains a script-generated list of workdays which excludes Saturday, Sunday and any dates in the Holiday file.

The whole thing is fairly simple, so you can snoop through it to see how it all works.

If anything is unclear, feel free to post your questions/bug reports.

PS. I should just add that the Workdays file contains workdays for the year 2002 only. If you want to test it for this year or other years, you will need to run the script that generates the workday records.

Workdays.zip

[Lee Smith]

Hi Bob,

Thanks for sharing, I'll snoop through them in the AM.

Lee

[buildude]

Hey Bob, I can see from all your posts that you're a mainstay in this great resource. For the multitudes that have undoubtedly been helped, THANKS. And by the way thanks for the workdays file share. Sincerely, Peter

[ozsprings]

These files are really great for all dates except... I have the finish date and I need to find out the date 20 working days (ie excluding holidays and weekends) before the finish date. I have tried putting in -20 in the DurationDays field but this does not work.

Thanks for any help

Oz

[BobWeaver]

I just tried this and it worked for me. Put your finish date in the Start Date field, and -20 in the duration. The end date should display a date that is 20 working days earlier.

Did you remember to generate the workday list for the period you are working with?

BTW I should just add that you can customize these files to do most date/duration calculations. The workdays file simply contains a list of all the workdays for the period you are concerned with. Each workday has a sequential workday number starting at 1, and the actual date of the corresponding workday. The schedule file has two relationships to the workday file. One is based on workday number, and the other is based on date. So, to do duration calculations, use the date relationship to find the workday numbers of your actual dates. Then you can add or subtract workdays to get durations, or add or subtract offsets from a workday number to get a new workday number which you can then use in the workday number relationship to find the actual date. In other words, take your actual dates and use the date relationship to convert to workday numbers. Do the calculation, and then use the workday relationship to convert back to actual date. There are a lot of options here.

[kcjones76]

Okay, I've sort of have it working in my database. My question is how can I have the calculation work for already existing records. I've tied "dStart" and "dEnd" to creation date and modification date respectively. I will be using it in a slighty different manner than the use above.

How do I populate these two fields for previous records?

[BobWeaver]

The files have a couple of lookups that adjust non-working days to the next or previous valid working days. This is in case a user enters a weekend date as a start or end date. dValidStart would be adjusted to the following Monday, and dValidEnd would be adjusted to the previous Friday. You will have to do a relookup on both the dStart and dEnd fields for the old records.

[Pacmersol]

Hi everyone, I'm new to the forum but have been using fm for a few years now.

I am working on FM 5.5 Developer and my problem is this:

I am building a Scheduler (one part of a larger project) for film production and what I have is:

1- two date fields- Shooting Date Start and Shooting Date Finish.

2- A numeric "Prep Days Required" that once entered should backwards

calc from the Shooting Date Start to give a "Prep Date Start"

(seems easy but wait...)

3- A numeric "Wrap Days Required" same as above but gives a date from

Shooting Date Finish forward to "Wrap Date Finish"

4- Radio Button: Work- Sat. Sun. or Both

Each Scene or Set will have a different Prep, Shooting, and Wrap Dates. As well each may have the variable to work weekends. I only want to input in the 4 fields above and have a formula(s) to calculate the rest.

If Shooting goes over a weekend it is not an issue because the start and finish are hard stamped. It is the Prep and Wrap that must calculate forward or backwards depending on the 5 or 6 day week variable. A 6-day work week can also be: mon-fri & sun and start monday again.

I've been all over the forum, and gained a better understanding but no real solution. My project is getting large 17 files not including runtime...can this be done with just formulas?

much thanx for any help

Randy

[BobWeaver]

There are some formulas that will do this if you don't need to account for statutory holidays. I know there was one on The Filemaker Inc. knowledge base.

http://www.filemaker.com/ti/102628.html

And, I'm pretty sure that someone posted a sample somewhere on this forum too, using formulas. Did you try doing a search with the search text "Workdays" or "Working days" ?

[Ricardo]

hey Bob,

is there a way that this can be used to calulate days late, to exclude weekends and workdays.

Your sample files calculates wheen a job is next due (date)id like to be able to calculate how late a job is (number)?

Thanks in advance,

Rich.

[Ken Tuley]

Hi, just wanted to say I just found this solution (just now needed it) and the files were extremely helpful. It should be gratifying to know they are still providing help years after they were first posted.

Thanks!!

Ken