Software

Ask something
Discussion Locked

Access 2000: Date Query?

0 helpful
3 replies

by michaelh

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I'm not sure where to begin in creating a query that will give me the turnaround days on two dates that would exclude weekends. Any help would greatly be appreciative.

Michael

3 replies

by chainsawz

VBA and Access do not provide any support for working with the typical workdays (Monday through Friday) like Excel's WorkDay function. However, here's a function written by Dev Ashish, which is posted at http://www.mvps.org/access/datetime/date0006.htm that might help you. (remove any spaces in url)
********* Code Start **************
Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate,EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
'*********** Code End **************

This discussion is closed to new replies.