Results 1 to 5 of 5
  1. #1
    aaslezak is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2015
    Posts
    34

    Question Work Days


    How can I calculate the difference between two dates but I only want to count the work days? So if was today and I wanted to go until 6/15/2015 the difference would be 5 and not 7 because I do not want to count Saturday or Sunday. Is there a special %datediff function where I would only count work days?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Common topic. Search forum or web. Here is one https://www.accessforums.net/modules...ion-48834.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    aaslezak is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2015
    Posts
    34
    I found the function but I do not think I created it correctly. I created it as a module I assume that is the correct way.

    Then I tried to used it but it says it does not recognize the function Work_Days. Here is how I use it:

    Color Status Value: ((Work_Days(Date(),[Implementation Date]))*24)-([SumOfRemaining Hours])

    Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

    Dim WholeWeeks As Variant
    Dim DateCnt As Variant
    Dim EndDays As Integer

    On Error GoTo Err_Work_Days

    BegDate = DateValue(BegDate)
    EndDate = DateValue(EndDate)
    WholeWeeks = DateDiff("w", BegDate, EndDate)
    DateCnt = DateAdd("ww", WholeWeeks, BegDate)
    EndDays = 0

    Do While DateCnt <= EndDate
    If Format(DateCnt, "ddd") <> "Sun" And _
    Format(DateCnt, "ddd") <> "Sat" Then
    EndDays = EndDays + 1
    End If
    DateCnt = DateAdd("d", 1, DateCnt)
    Loop

    Work_Days = WholeWeeks * 5 + EndDays

    Exit Function

    Err_Work_Days:

    ' If either BegDate or EndDate is Null, return a zero
    ' to indicate that no workdays passed between the two dates.

    If Err.Number = 94 Then
    Work_Days = 0
    Exit Function
    Else
    ' If some other error occurs, provide a message.
    MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

    End Function

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Where did you put that function - a general module? Make sure the function is not same name as the module.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-10-2015, 03:51 AM
  2. Replies: 4
    Last Post: 12-06-2014, 08:49 PM
  3. Replies: 1
    Last Post: 03-27-2014, 06:43 PM
  4. Select Dates between days and two days ago?
    By rstonehouse in forum Queries
    Replies: 4
    Last Post: 08-18-2010, 02:13 AM
  5. less than 180 days old
    By techexpressinc in forum Queries
    Replies: 2
    Last Post: 06-04-2009, 04:29 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums