# Thread: DateDiff that can factor out Sat & Sun??

1. Registered User
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. Registered User
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. Registered User
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. Registered User
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
•