Calculate Dates, work days only, exclude holidays

[severts]

For the last two weeks I've been combing through the posts here, trying to find a solution for my date calculation challenge. There are many great ideas and I have them working, but I'm wondering if there is a better way. I am attempting to create a production schedule for a book. All the dates in the production cycle are based on the last date (catalog date). Ultimately, I want to enter the Catalog Date and have the other date fields fill in based on it.

For instance, the Delivery Date is 1 month prior to the Catalog Date. The To Printer Date is 4 weeks prior to the Delivery Date. The To Production Date is 1 week prior to the To Printer Date. And so it goes for a string of 11 dates.

I have a separate table with the holidays listed. I also have a separate field to indicate number of working days between each date.

My question is, can I do this without the separate field for working days and still make sure that the date arrived at is a week day and holidays are not included in the calculation?

Here is the calculation I have arrived at so fra.

Catalog Date - Int(Delivery Days/5)*7-Middle ("00000034560145601256012360123400000"; Mod (Catalog Date - Date (1;3;1904);7)*5 + Mod (Delivery Days; 5) +1;1) - (Lookup High-Lookup Low)

The Lookup High and Lookup Low fields relate to a counter for the holidays (found this idea somewhere in this forum).

This is my first post, so I appreciate any help you can give!

[comment]

I don't think you can this do arithmetically. With holidays, you need a recursive calculation, because (a) some holidays can fall on a weekend, and (: you cannot tell how many holidays are in a range while the range itself is being computed.

So basically you need a custom function, or (if you don't have the Developer/Advanced version) a script, to go over the dates one-by-one and check each date. If the date is not a weekend and it's not a holiday, you count it and move on to the next one - until you have counted the required amount of working days.

I didn't get the problem with "the separate field for working days".

[severts]

Thanks for the response. I was hoping not to have to create more fields to hold the number of working days - but rather have the calculation for each date take care of that.

For the holidays...my solution (not really a technical solution) is that even if a holiday falls on a weekend, my company still observes it and gives days off. So, my table would actually contain the date we get off. I think this would take care of it.

[comment]

even if a holiday falls on a weekend, my company still observes it and gives days off. So, my table would actually contain the date we get off. I think this would take care of it.

That would take care of (a), but not of (:.

I was hoping not to have to create more fields to hold the number of working days - but rather have the calculation for each date take care of that.

It's not a good idea to hard-code data into calculations. What if the requirements change one day? You can have this information tucked out of sight in a couple of global fields, even in another table (like a Preferences table).