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!
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!
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.
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
Nope, I am in the US. Thanks!
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]
Scrub that; I forgot that NETWORKDAYS isn't a standard function and is part of the Analysis Toolpak.Originally Posted by Abulafia
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.
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
YUK...sorry for the terrible post!
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
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