# Thread: SOLVED: Function needed for business days

1. ## 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. 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. 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.

4. Nope, I am in the US. Thanks!

5. 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. 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. 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

8. YUK...sorry for the terrible post!

9. 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

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

#### Posting Permissions

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