Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Excluding weekends in DateDiff (97)

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

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excluding weekends in DateDiff (97)

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

  4. #4
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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
  •