help with week days

[Jim]

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

[Bob Quintal]

"Jim" <ji**@pioneers.cawrote in
news:xcWAj.64813$w94.37134@pd7urf2no:

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

there have been some deep discussions of this on this list over tha
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

[DavidBoyle via AccessMonster.com]

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

>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

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

[Ron2006]

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