Excluding weekends and holidays from date calculations

[Guest]

Hi - I have a client who would like a report comparing start date and
completion date for various tests to determine how long it took to complete
each test. The calculation needs to exclude weekends and holidays. Can
anyone tell me if there is a way to do this and, if so, how?
Thank you much.

 

[SA]

Kt:

If you stop by our web and look in the free files area, there's a download
called VacHours that contains a function to determine the number of hours
between two dates (inclusive) excluding holidays and weekends. Should fit
the bill.

 

[Guest]

Hi - Thanks so much to the information. I will see if I can figure this out.
I don't write much code - mainly try to accomplish everything through
queries. I'll see if this is too far over my head.
Thanks again.

 

[Guest]

I can see some of what you're doing, but I don't understand what the schedule
table is used for, we don't have some Fridays off, I'm trying to calculate
days, not hours and lastly, I wouldn't know what to do with this module if I
could modify it correctly. Sorry to be such a newbie - I'm just trying to
get a report based on a query. I can't believe MS hasn't found a better
solution for this. It seems like a request that would be fairly common. If
anyone has a solution a newbie can understand, I'm listening! Thanks again,
though, for your response.

 

[SA]

KT:

The schedule table is used only for the form and not the actual CalcVachours
function itself (which only uses the holidays table). The schedule table
only determines the number of fridays off for a specific demo'd schedule.
So, in your query, you could simply call the function always specifying
schedule 3 (normal 5 day 80 hour period) with a plan (schedule) start date
of the monday of any preceding period start, like this in the immediate
window:

?CalcVacHours("2/6/2006","2/17/2006","1/30/2006",3)

or like this in your query:

TotAvailHours:
CalcVacHours([PeriodStartField],[PeriodEndField],[FirstMondayStaticValue],3)

Simply import the VacHours module into your db for it to work.

HTH