Consulting

Results 1 to 10 of 10

Thread: SOLVED: Function needed for business days

  1. #1
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location

    Question SOLVED: Function needed for business days

    Hi there...can anyone tell me the Access equivalent for Excel's NETWORKDAYS function? I'm trying to use DATEDIF, but I don't see an option to include a list of holidays.

    Thanks!

  2. #2
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Are you in the UK or elsewhere? If you are in the UK I can give you something that will calculate all working days and remove public holidays from the equation.

    If not, I can give you the basic workdays formula. I also have some US holiday formulae.

    Another method is to set a reference to Excel and make use of the NETWORKDAYS formula.

  3. #3
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    I'm afraid there is no equivalent in Access. You need to create your own table of holidays and write your own function. I'll knock something up and post back - if no one else does so first.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  4. #4
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    Nope, I am in the US. Thanks!

  5. #5
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    Tony...I've seen the following from another board, but I can't quite see how to post the holidays:

    [VBA] Function NetWorkDays(Start_Date As Date, End_Date As Date, Holidays() As =
    Variant) As Integer
    Dim intX As Integer
    Dim intCount As Integer
    Dim intUbound As Integer
    Dim tempEnd_Date As Date
    intX =3D DateDiff("d", Start_Date, End_Date)
    intCount =3D 0
    Do Until intCount =3D intX
    Select Case WeekDay(DateAdd("d", intCount, Start_Date))
    Case vbSaturday, vbSunday
    intX =3D intX - 1
    End Select
    intCount =3D intCount + 1
    Loop
    tempEnd_Date =3D DateAdd("d", intCount, Start_Date)
    For intX =3D LBound(Holidays) To UBound(Holidays)
    If Holidays(intX) >=3D Start_Date And Holidays(intX) <=3D =
    tempEnd_Date Then
    intCount =3D intCount - 1
    End If
    Next
    NetWorkDays =3D intCount
    End Function
    [/VBA]

  6. #6
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Quote Originally Posted by Abulafia
    Another method is to set a reference to Excel and make use of the NETWORKDAYS formula.
    Scrub that; I forgot that NETWORKDAYS isn't a standard function and is part of the Analysis Toolpak.

    Have a look at Pat Hartman's database on this forum.

    This extra post by myself here may also be of some interest to you.

  7. #7
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    Abulafia...the function you pointed me to works great. I think this thread can be considered SOLVED. But, the user will have to create a new table called tblHolidays (below) within the same DB as the code for it to work. Easy enough to do. THANKS!!!!

    1/1/20015/28/20017/4/20019/3/200111/22/200111/23/200112/25/20011/1/20025/27/20027/4/20029/2/200211/28/200211/29/200212/25/2002
    1/1/20035/26/20037/4/20039/1/200311/27/200311/28/200312/25/20031/1/20045/31/20047/5/20049/6/200411/25/200411/26/200412/24/200412/31/2004
    Last edited by TonyJollans; 07-23-2004 at 07:02 AM. Reason: Break long string

  8. #8
    VBAX Regular babsc01's Avatar
    Joined
    Jun 2004
    Location
    Southaven, MS
    Posts
    53
    Location
    YUK...sorry for the terrible post!

  9. #9
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    babsc01 - that routine takes in an array of holidays, something like ..

    [VBA] Dim Holidays(2) as Date
    Holidays(0) = #01 Jan 2004#
    Holidays(1) = #25 Dec 2004#
    Holidays(2) = #26 Dec 2004#
    [/VBA]
    Then call like ..

    [VBA] NetWorkDays(StartDate, EndDate, Holidays)[/VBA]
    .. or without declaring your own array, you can just do ..

    [VBA]
    NetWorkDays(StartDate, EndDate, Array(#01 Jan 2004#, #25 Dec 2004#, #26 Dec 2004#))
    [/VBA]

    Seems little point in me knocking anything up now
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  10. #10
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    I edited your post - just broke the long string in two
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

Posting Permissions

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