Count Days Excluding Weekends

  • Thread starter Thread starter Jenn
  • Start date Start date
J

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
 
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;
 
Back
Top