How to exculde Weekend from process durtation

ahmed shahrani
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)
any help please

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

Open in new window

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.

Author

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

Open in new window

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
John TsioumprisSoftware & Systems Engineer

Commented:
You could state that first day of the week is Sunday ...like this
weekday(date,vbSunday)

Open in new window

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.

Open in new window

with:

            Select Case Weekday(Date1)
                Case vbFriday, vbSaturday
                    ' Skip weekend.

Open in new window

and then use this expression using Date() for a Null value of End Date:

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

Open in new window

Do more with

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

Start 7-Day Free Trial