By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,477 Members | 1,641 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,477 IT Pros & Developers. It's quick & easy.

help with week days

P: n/a
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

Mar 9 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"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

Mar 9 '08 #2

P: n/a
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

Mar 10 '08 #3

P: n/a
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
Mar 10 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.