Date Difference Excluding Weekends and Holidays

[LadyDi]

Is there a way, in a query or via the use of a module, to calculate the workdays between two dates excluding holidays without needing to maintain a separate table with the holidays listed out?

For example, I would like it to calculate 21 work days for the month of May. However, I don't want to have to go in the first of each year and manually list out all the holidays for the year. Is there a way for the Holidays table to just contain the number of holidays in a given month (i.e. in December we get 3 holidays (Christmas Eve, Christmas Day, and New Years Eve)

 

[JHB]

Yes it is, but how about Easter it is moving around?
Why do you not want a separate table with the holidays listed out, you can copy it year for year (manually or by code) and only refresh it where it change.

 

[LadyDi]

I was just trying to make this database as automated as possible. That's why I was trying to avoid the need to update a Holidays table once a year.

You mentioned that this was possible. Could you tell me what I need to do? If there is not a way to avoid a Holiday table, then I will add it.

 

[namliam]

http://www.access-programmers.co.uk/forums/showthread.php?t=174215&highlight=Holiday

http://www.access-programmers.co.uk/forums/showthread.php?t=238821&highlight=Holiday

Have a look there, both seem popular
Excluding weekends is something you dont need to do in the holidays table is quite easy to do in a function.
Same goes for fixed days like XMas and New years, even some "floating" days like easter and whit monday can be calculated quite easily. Some days like "first monday in august" or simular things can be done as well.

I hope the links help you get started.

 

[ypma]

quote "I was just trying to make this database as automated as possible. That's why I was trying to avoid the need to update a Holidays table once a year."

Just a thought
Religious and celebration holiday dates can be forecast a number of years into the future and can be added to your current holiday table to avoid have to update each year; all future dates outside your set parameter will be ignored. Unforeseen holidays can be added to the table with little effort.

Regards Ypma

 

[LadyDi]

The link with the Master Dates worked great. Thank you very much for you assistance.