count days between dates excluding weekends and holidays

[mleeman]

Maybe there is an easier way to do this. We need to track staff time off and I was using excel and had a separate worksheet for each employee and this worked fine, but now they want more reports showing the data various ways and I thought it was to hard to display the data the ways they need it, also they will add new reports later.

I put the data in to Access and I need a report to show each employee and count the number of days taken off between the fields [StartDate]and [EndDate] and to exclude weekends and holidays from those dates. I use DateDiff("d",[StartDate],[EndDate])+1, this accurately counts the days but if the date range includes a weekend or holiday it is also counted. I need it to exclude weekends and holidays in the count.

The function in Excel is "NetWorkDays" but I can not find similar in access.

 

[67peshawar294]

thread181-26850

 

[MichaelRed]

faq181-261




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Creosote65]

I have tried this function as a subroutine in a form with two text boxes (start, end), and it worked, returning a value excluding holidays and weekends.

How would I apply this to a query result in which I have several records with two dates (i.e. start date, end date) and wanted to calculate the number of days between the two with the calculated exclusion? I would want to output my results, including the calculations, to a data table. It would have several records...

Creosote

 

[MichaelRed]

as a calculated column. info on the syntax can by found via the ubiquitous {F1} (aks H E L P)







MichaelRed

 

[floydpepper]

MichaelRed,
How could I apply this to a report or query? How would I call/activate this function? I'm a beginner, so I appreciate your patience.

FloydPepper

 

[MichaelRed]

There are a number of other threads re this topic (not just the faq) and the first thing anyone should do is become aware of the reasonable resources available in their endevors. Here (Tek-Tips) one of these is the ability to search i a number of ways. I would suggest that you avail yourself of the opportunity not just to ferret out the single answer, but to learn how to effectively use these (and other resources).




MichaelRed