SOLVED: Function needed for business days

[babsc01]

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!

[SJ McAbney]

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.

[TonyJollans]

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.

[babsc01]

Nope, I am in the US. Thanks!

[babsc01]

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

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 tags courtesy of www.thecodenet.com

[SJ McAbney]

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.

[babsc01]

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

[babsc01]

YUK...sorry for the terrible post!

[TonyJollans]

babsc01 - that routine takes in an array of holidays, something like ..

Dim Holidays(2) As Date 
Holidays(0) = #01 Jan 2004# 
Holidays(1) = #25 Dec 2004# 
Holidays(2) = #26 Dec 2004# 

VBA tags courtesy of www.thecodenet.com

Then call like ..

NetWorkDays(StartDate, EndDate, Holidays) 

VBA tags courtesy of www.thecodenet.com

.. or without declaring your own array, you can just do ..

 
NetWorkDays(StartDate, EndDate, Array(#01 Jan 2004#, #25 Dec 2004#, #26 Dec 2004#)) 

VBA tags courtesy of www.thecodenet.com

Seems little point in me knocking anything up now

[TonyJollans]

I edited your post - just broke the long string in two