# Thread: Excluding weekends in DateDiff (97)

1. ## Excluding weekends in DateDiff (97)

I would like to take the difference between two dates, but exclude weekends and holidays. I use the Datediff but can't exclude weekends. Thanks in advance. Deb

2. ## Re: Excluding weekends in DateDiff (97)

I believe that DateDiff can show you the Weekdays by just specifying "w" as the parameter.
Pat

3. ## Re: Excluding weekends in DateDiff (97)

You might want to take a look at this thread where a similar problem was discussed.

4. ## Re: Excluding weekends in DateDiff (97)

I tried that and it cut my number more than half so I did some research and help explains the w as the number of weeks, not weekdays. Thanks for your reply.

5. ## Re: Excluding weekends in DateDiff (97)

You figured that out before I got a chance to reply, but with that you can compute a fairly accurate number of workdays by determining the number of weekends that occur in the interval, multiplying by 2 and subtracting it from the number of days between the two dates. If you want to get really fancy, a holiday table will let you determine the number exactly.

6. ## Re: Excluding weekends in DateDiff (97)

I thought "ww" was weeks not weekdays. Help shows weekdays as "w".
The following help is from Access 97.
Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

Pat

7. ## Re: Excluding weekends in DateDiff (97)

Correct, but weekdays definition says, number of weeks between two starting points. I got that right out of the help of Access.

8. ## Re: Excluding weekends in DateDiff (97)

You are quite right, I misunderstood what "w" Weekday meant.
I now understand what it means.
Pat <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

9. ## Re: Excluding weekends in DateDiff (97)

Here is another example of a function you can use to calculate workdays, with or without a holiday table:
<pre>Public Function NetWorkDaysMod(StartDate As Date, EndDate As Date, _
Optional HolidayTbl) As Long
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strMsg As String
Dim lngStart As Long
Dim lngEnd As Long
Dim lngCount As Long
Dim n As Long

lngStart = CLng(StartDate)
lngEnd = CLng(EndDate)

For n = lngStart To lngEnd
Select Case Weekday((n))
Case 2 To 6 'Mon - Fri
lngCount = lngCount + 1 'increment by 1
Case 1, 7 'Sat, Sun
'do not increment
End Select
Next n

If IsMissing(HolidayTbl) Then
NetWorkDaysMod = lngCount
Else
'Optional HolidayTbl argument = name of Holidays table if one available
'HolidayDate is Date field in Holidays table (tblHolidays)
Set db = CurrentDb
strSQL = "SELECT * FROM " & HolidayTbl & " " & _
"WHERE HolidayDate BETWEEN #" & StartDate & "# AND #" & EndDate & "#;"
Set rst = db.OpenRecordset(strSQL)
If Not rst.EOF Then
rst.MoveLast
End If
NetWorkDaysMod = lngCount - rst.RecordCount
End If

Exit_Sub:
If Not rst Is Nothing Then rst.Close
Set db = Nothing
Set rst = Nothing
Exit Function
Err_Handler:
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "NETWORKDAYS FUNCTION ERROR"
Resume Exit_Sub

End Function</pre>

For example, if you specify StartDate of 1/01/2002 & EndDate of 12/31/2002, function will return 261, or 251 w/a Holidays table (mine uses the 10 "official" US Federal holidays).

I've seen other methods for doing this, but this was simplest way I could think of. As alternative you could use the Excel NETWORKDAYS function, you would have to set a reference to the MS Office Web Components (OWC) Function Library (MSOWCF.DLL) rather than Excel, because this function is not included in the Excel WorksheetFunction class; it's part of the Excel Analysis ToolPak add-in. Simplified example (w/o optional argument):
<pre>Public Function atpNetWorkDays(StartDate As Date, EndDate As Date)
Dim ATP As New MSOWCFLib.OCATP
atpNetWorkDays = ATP.NETWORKDAYS(StartDate, EndDate)
Set ATP = Nothing
End Function</pre>

The OWC type library includes many of the ATP functions. However, if you are still using Office 97 you probably don't have the OWC installed; it's installed by default w/Office 2K & later.

HTH

10. ## Re: Excluding weekends in DateDiff (97)

To elaborate further, the DateDiff Interval setting descriptions for w (Weekday) and ww (Week) are somewhat misleading. If the Interval setting is "w", DateDiff returns the number of weeks between date1 and date2, counting the occurences of the day on which date1 falls, up to & including date2, but not including date1. It does not return the number of weekdays between two dates, as the setting name implies. If the Interval setting is "ww", DateDiff returns the number of calender weeks between date1 and date2 by counting the number of Sundays (the default, or the day of week specified as first day of week by the optional firstdayofweek argument) between date1 and date2. If date2 falls on a Sunday, it is counted, but date1 isn't counted, even if it falls on a Sunday. Note that the firstdayofweek argument only applies when using the "ww" interval setting.

Example (using default firstdayofweek = Sunday):
<pre>? DateDiff("w",#10/07/2002#,#10/27/2002#)
2
? DateDiff("ww",#10/07/2002#,#10/27/2002#)
3 </pre>

Oct 7 is a Monday, Oct 27 is a Sunday. The first example returns 2 because there are only 2 Mondays in date range, not counting date1. The second example returns 3 because there are 3 Sundays in date range, counting date2.

Hope this clarifies things. Or maybe not.... <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

#### Posting Permissions

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