Calculating Dates minus weekend

[Jeff F]

I need to calculate multiple dates so I can track processing time using
Access query. I know that DatD if will calculate the days between the dates
but I need to subtract the weekends. What is the best way to do this?


Thanks
Jeff Forrester

 

[bhicks11 via AccessMonster.com]

Hi Jeff,

Here is a link to an article/code I found for this. Hope it helps.

http://www.tek-tips.com/faqs.cfm?fid=261

Bonnie
http://www.dataplus-svc.com

 

[raskew via AccessMonster.com]

Hi -

Try copying/pasting this to a standard module, then call it as shown in the
examples. Note that business days are defined as Monday - Friday. In this
example, holidays are not considered.

'*******************************************
Public Function UpBusDays3(pstart As Date, _
pNum As Integer, _
Optional pAdd As Boolean = True) As Date
'*******************************************
'Purpose: Add or subtract business days
' from a date
'Coded by: raskew
'Inputs: 1) +) ? UpBusDays3(#2/17/06#, 3, True)
' 2) -) ? UpBusDays3(#2/22/06#, 3, False)
'Output: 1) +) 2/22/06
' 2) -) 2/17/06
'*******************************************

Dim dteHold As Date
Dim i As Integer
Dim n As Integer

dteHold = pstart
n = pNum
For i = 1 To n
If pAdd Then 'add days
dteHold = dteHold + IIf(WeekDay(dteHold) > 5, 9 - WeekDay(dteHold),
1)
Else 'subtract days
dteHold = dteHold - IIf(WeekDay(dteHold) < 3, Choose(WeekDay
(dteHold), 2, 3), 1)
End If
Next i
UpBusDays3 = dteHold

End Function
'*******************************************

HTH - Bob