Excel Hints

Results 1 to 10 of 10

Thread: SOLVED: Function needed for business days

  1. #1

    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
    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,290
    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
    Nope, I am in the US. Thanks!

  5. #5
    Tony...I've seen the following from another board, but I can't quite see how to post the holidays:

    VB:
    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 
    
    
    Formatting tags added by mark007

  6. #6
    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
    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
    YUK...sorry for the terrible post!

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

    VB:
    Dim Holidays(2) As Date 
    Holidays(0) = #01 Jan 2004# 
    Holidays(1) = #25 Dec 2004# 
    Holidays(2) = #26 Dec 2004# 
    
    
    Formatting tags added by mark007
    Then call like ..

    VB:
    NetWorkDays(StartDate, EndDate, Holidays) 
    
    
    Formatting tags added by mark007
    .. or without declaring your own array, you can just do ..

    VB:
     
    NetWorkDays(StartDate, EndDate, Array(#01 Jan 2004#, #25 Dec 2004#, #26 Dec 2004#)) 
    
    
    Formatting tags added by mark007
    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,290
    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
  •