Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    6

    Unanswered: DateDiff that can factor out Sat & Sun??

    Is there a DateDiff function that will produce Business working days as opposed to Calendar days?

    Thanks,

    Kevin

  2. #2
    Join Date
    Mar 2004
    Posts
    42
    DateDiff("d",[Date1],NZ([Date2],Now()))-(DateDiff("ww",[Date1],NZ([Date2],Now()))*2)

    This will calculate working days only removing the weekend date but not holidays. This will account also for any null date on your record which will default to current date.

    HTH

  3. #3
    Join Date
    May 2005
    Posts
    119
    I've actually created my own function for this. You're welcome to use:

    Function workdays(startdate As Date, enddate As Date) As Integer
    Dim tempdate As Date, I As Integer
    If startdate > enddate Then
    workdays = -1
    Exit Function
    End If
    tempdate = startdate
    Do Until tempdate > enddate
    Select Case Format(tempdate, "w")
    Case vbSaturday, vbSunday
    I = I
    Case Else
    I = I + 1
    End Select
    tempdate = tempdate + 1
    Loop

    workdays = I
    End Function

    Sample call: workdays(#10/1/05#,#10/31/05#)

    The only caveat is, if you're specifically looking at excluding business days, then you probably want to exclude holidays (specific to your compnay) as well. In this case, you may want to build a table of all calendar days and for each day you could assign a value for weekday, weekend or holiday. You then compare your tempdate to the calendar table and change your case statement accordingly.

    I hope this helps!
    Krista

  4. #4
    Join Date
    Oct 2005
    Posts
    6
    Thank you very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •