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

[SJ McAbney]

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 (http://www.access-programmers.co.uk/forums/showthread.php?t=62570).

This extra post by myself here (http://www.access-programmers.co.uk/forums/showthread.php?t=54222) 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#

Then call like ..

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


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


Seems little point in me knocking anything up now :)

[TonyJollans]

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