How to exculde Weekend from process durtation

ahmed shahrani used Ask the Experts™
on
Hi,
I would like to calculate the process duration and how long does it take excluding Weekend ( Friday and Saturday), I have used below function but it does not work specially if End date is "empty" and the process not completed... what i what to do is calculate the duration ( End date-Start date) excluding weekend(fri-Sat) and if end date is empty, (up-to-date - start date)

Public Function WorkdayDiff(ByVal d1 As Date, ByVal d2 As Date) As Long
Dim diff As Long, sign As Long
Dim wd1 As Integer, wd2 As Integer

diff = DateDiff("d", d1, d2)
If diff < 0 Then
'* Effectively swap d1 and d2; reverse sign
diff = -diff
sign = -1
wd1 = Weekday(d2)
Else
sign = 1
wd1 = Weekday(d1)
End If
wd2 = (wd1 + diff - 1) Mod 7 + 1

If (wd1 = 1 And diff = 0) Or (wd1 = 7 And diff <= 1) Then
WorkdayDiff = 0 '* Both dates are on same weekend
Exit Function
End If

'* If starting or ending date fall on weekend, shift to closest weekday
'* since the weekends should not contribute to the sum.
'* This shift is critical for the last If condition and arithmetic.
If wd1 = 7 Then
wd1 = 1 '* Shift to Monday
diff = diff - 1
ElseIf wd1 = 6 Then
wd1 = 1 '* Shift to Monday
diff = diff - 1
End If

If wd2 = 1 Then
diff = diff - 1 '* Shift to Friday
ElseIf wd2 = 7 Then
diff = diff - 2 '* Shift to Friday
End If

'* If difference goes beyond weekend boundary then...
If diff >= 7 - wd1 Then
'* Normalize span to start on Monday for modulus arithmetic
'* then remove weekend days
diff = diff - ((diff + (wd1 - 2)) \ 7) * 2
End If

WorkdayDiff = sign * (diff + 1)
End Function
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
Is up-to-date the same as current date (today's date)?
Top Expert 2014

Commented:
The first thing is to make the d2 parameter optional defaulting to Date().
Top Expert 2014

Commented:
Your code is checking for the Weekday() value of 1 (Sun) or 7 (Sat) for same weekend calculation.  You state that your weekend is Fri-Sun.  Please explain.
Top Expert 2014

Commented:
Your shift-forward code doesn't include Sunday.  I'm not sure the diff = diff - 1 statement is aligned with your shift.
Top Expert 2014

Commented:
Your shift code for weekday2 drops back to Friday, which is still part of your defined weekend.
Top Expert 2014

Commented:
This iteration code seems to work.
``````Function Q_29163646(ByVal d1 As Date, Optional ByVal d2 As Date) As Long
Dim dtThing As Date
Dim lngStep As Long

If d2 = Empty Then
d2 = Date
End If

lngStep = Sgn(DateDiff("d", d1, d2))

For dtThing = (d1 + lngStep) To d2 Step lngStep
Select Case Weekday(dtThing)
Case 1, 6 To 7  'exclude Fri-Sun weekends
Case Else
Q_29163646 = Q_29163646 + 1
End Select
Next
End Function
``````
Top Expert 2014

Commented:
My routine makes an assumption that the parameters passed are 'valid' dates.  It may be possible that a zero date (year = 1899) is passed, so you might want to add some date range validation to the code.

Commented:
Thanks dear... the weekend is Friday and Saturday.. not Sunday
Top Expert 2014

Commented:
``````Function Q_29163646(ByVal d1 As Date, Optional ByVal d2 As Date) As Long
Dim dtThing As Date
Dim lngStep As Long

If d2 = Empty Then
d2 = Date
End If

lngStep = Sgn(DateDiff("d", d1, d2))

For dtThing = (d1 + lngStep) To d2 Step lngStep
Select Case Weekday(dtThing)
Case 6 To 7  'exclude Fri-Sat weekends
Case Else
Q_29163646 = Q_29163646 + 1
End Select
Next
End Function
``````
Distinguished Expert 2017

Commented:
This database includes many useful date functions including one that excludes weekends and holidays.  If your weekend is not Sat-Sun, you will need to modify the code.  If you want to include holidays, add them to the holiday table.  Otherwise, just remove all the rows from the holiday table and just leave the code as it is.  No holidays will be found so no days will be excluded.
UsefulDateFunctions180618.zip
Software & Systems Engineer

Commented:
You could state that first day of the week is Sunday ...like this
``````weekday(date,vbSunday)
``````
and just reject everything bigger than 5
Friday =6
Saturday =7
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can use my function here:

Workdays between 2 dates

Just replace this:

``````            Select Case Weekday(Date1)
Case vbSaturday, vbSunday
' Skip weekend.
``````
with:

``````            Select Case Weekday(Date1)
Case vbFriday, vbSaturday
' Skip weekend.
``````
and then use this expression using Date() for a Null value of End Date:

``````Workdays = DateDiffWorkdays([Start Date], Nz([End Date], Date()))
``````

Do more with

Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.