Count Days Excluding Weekends

[Jenn]

I need to count the days an employee is absent, based on the beginning date
of absence and day returning to work. I know that there is a workweek
function, but I'm not sure how I can use it in this instance...see the
example.

If an employee is absent 8/15-8/20, the following criteria yields 5 days,
but a weekend was included in the equation.

[Date Returning to Work] - [Beginning Date of Absence]=5


Any help would be greatly appreciated!

Thanks,
Jenn

 

[Douglas J. Steele]

Take a look at the Date/Time section of "The Access Web"
http://www.mvps.org/access/

There are a couple of different solutions there.

 

[John Verhagen]

This seems to work for the small set of values I tried with dates that have
no time components, just don't put weekend dates into your table:
SELECT tblAbsence.[Beginning Date of Absence], tblAbsence.[Date Returning to
Work], (([Date Returning to Work]-[Beginning Date of
Absence])\7)*5-(Weekday([Date Returning to Work])>=Weekday([Beginning Date
of Absence]))*(Weekday([Date Returning to Work])-Weekday([Beginning Date of
Absence]))-(Weekday([Date Returning to Work])<Weekday([Beginning Date of
Absence]))*(5+Weekday([Date Returning to Work])-Weekday([Beginning Date of
Absence])) AS Absence
FROM tblAbsence;