"Jim" <jimt (AT) pioneers (DOT) ca> wrote in
news:xcWAj.64813$w94.37134@pd7urf2no:

last few years.

Google for the string "SetHoliday_DayOff". That's a function that
you could modify to tell you how many holidays exist. as for total
days, you use the datediff() function, not count(). You'd need to
wrap that in an user-defined function that removes 2/7ths makes
adjustments for the first and last weeks, and then deducts the
holidays.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Guys I'm rusty. Haven't messed with Access in a while.

I'm trying to determine how many workdays (or weekdays) between two dates.

I don't think their are any "canned" functions within access - or is there?

Can someone help with a simple function

count(vbweekdays, startdate,enddate), less stat holidays (LOL!)

JT

lots of useful info here: http://mvps.org/access/datetime/index.html


Jim wrote:
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200803/1

Here is one solution:

'--------------------------------------------------------------------------*-------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding
Saturdays,
' : Sundays, and any days in the Holidays table
'--------------------------------------------------------------------------*-------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer


On Error GoTo CalcWorkDays_Error


CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate]
between
#" _
& dtmStart & "# And #" & dtmEnd & "#")


CalcWorkDays_Exit:


On Error Resume Next
Exit Function


CalcWorkDays_Error:


MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit


End Function
===========================

in first Calcworkdays step
datediff 1 says how manu days between dates
datediff 2 says how many Saturdays (and subtracts them)
datediff 3 says how many Sundays (and subtracts them)
+ 1 says to count from AND to date. (Gives you an answer of 1
workday if from and to date are same)

Second Calcworkdays step then subtacts the number of holidays in that
time frame

Holiday table contains a date for every work/week day that is a
holiday (If 12/25 is a staturday then 12/24 would be in the table
since that is the workday that would be taken off in palce of 12/25)

===========================
Ron