General discussion

Locked

Access 2000: Date Query?

By michaelh ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 2000: Date Query?

by chainsawz In reply to Access 2000: Date Query?

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 **************

Collapse -

Access 2000: Date Query?

by michaelh In reply to Access 2000: Date Query?

Poster rated this answer

Collapse -

Access 2000: Date Query?

by michaelh In reply to Access 2000: Date Query?

This question was closed by the author

Related Discussions

Related Forums