Working Days Calculator

Is there any way of writing a function that will calculate the no of working days between two dates, WITHOUT looping through each row.

I am currently using this ...

Public Function GetNumberOfWorkDays(sStartDate, sEndDate)
Dim iDays
Dim iWorkDays
Dim sDay
Dim i

iDays = DateDiff("d", sStartDate, sEndDate)

iWorkDays = 0

For i = 0 To iDays
'First day of the week is sunday
If sDay <> 1 And sDay <> 7 Then
iWorkDays = iWorkDays + 1
End If
Next
GetNumberOfWorkDays = iWorkDays
End Function

This function works fine, however has one small glitch! it doesn't run too well if there 65000 rows to check through.. in fact it gets to the point of crashing the application. 65000 rows * the amount of loops for each one { depending on the difference between the dates } This doesn't take into account for holidays ... but i'm not worried about this for now i can build that in after.

Any ideas would be greatly appreciated..

thanks

Dave
LVL 1
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
My best bet would be a little math, - whether it will help you depends on how many days your are looping through now. The modified code below will loop 6 days as max.

for each set of 7 days we have 5 working days
for the remainder, we must identify the days individually

i.e.
we start on a Thursday and datediff gives us 27 days

first integer divide and multiply by 5
workingdays= 27\7*5
then walk the remainder:
remainder = 27 mod 7
from today till today + remainder
if workingday then increment workingdays

Public Function GetNumberOfWorkDays(sStartDate, sEndDate)
Dim iDays
Dim iWorkDays
Dim sDay
Dim i

iDays = DateDiff("d", sStartDate, sEndDate)

iWorkDays = (iDays \ 7) * 5
iDays = iDays mod 7

For i = 0 To iDays
'First day of the week is sunday
If sDay <> 1 And sDay <> 7 Then
iWorkDays = iWorkDays + 1
End If
Next
GetNumberOfWorkDays = iWorkDays
End Function

Regards,
Sven
0
Commented:
I think DateDiff uses "ww" to calculate weekdays ... perhaps this is a good place to start and then you could do a aggregate query to get the number of weekdat holidays duriong the time period, subtract and viola.

Steve
0
Commented:
(1)
Have a look at:
Ten Tips for Microsoft Access Developers (Microsoft Access 2002 Technical Articles)
Learn Access programming tips such as how to display a range of dates in a Calendar control, creating a blinking label prompt for a text box, converting a number from one base to another, and more. (39 print pages)
http://msdn.microsoft.com/library/en-us/dnacc2k2/html/odc_actips.asp

(2)
OR EVEN BETTER
You can utilise the specific Excel function NETWORKDAYS in your excel reference library

###########################################################
dblResult = Excel.Application.WorksheetFunction.NETWORKDAYS(start_date,end_date,holidays)
###########################################################

NETWORKDAYS(start_date,end_date,holidays)

Start_date   is a date that represents the start date. Dates may be entered as text strings within quotation marks (for example, "1/30/1998" or "1998/01/30"), as serial numbers (for example, 35825, which represents January 30, 1998, if you're using the 1900 date system), or as results of other formulas or functions (for example, DATEVALUE("1/30/1998")).

End_date   is a date that represents the end date.

Holidays   is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.

Regards,
John
0
Commented:

I would go with Steve's solution (although weekday is 'w' ('ww' is week)).

Using a standard VB function is more portable than using an Excel function (although I'm sure it will work just as well).

Sven
0
Commented:
stevbe
REF YOUR COMMENT: "I think DateDiff uses "ww" to calculate weekdays"

"ww" only counts the number of calendar weeks between two dates
"w" counts the number of weeks between two dates subtle difference.

John
0
Commented:
svenkarlsen
It doesn't get more portable. This is Access VBA, not VB6. The excel model IS IN THE ACCESS REFERENCE LIBRARY. It is optimised and more efficient / error proof than home grown code. You're not going to be very productive if you keep reinventing the wheel.

Regards,
John
0
Author Commented:
Thanks to all comments and answers guys .. i'm looking at all possibilities at present... keep watching this space.

Thanks

Dave
0
Commented:
butcherd11
NETWORKDAYS automatically takes care of leap years. I can also show you how to optionally add a list of holiday dates to take account of.
John
0
Commented:
A small note for butcherd11 :
make sure you tick the reference to Microsoft Excel in the Access references library (in vba editor click tools/references/Microsoft Excel)
regards,
John
0
Author Commented:
okay i have ticked the reference and added this function as provided by yourself john as above... i can't seem to get this to work ? undefined function .. tried all ways.. i'm clearly doing something wrong.
0
Commented:
TRY IT IN THIS FORMAT
dblResult = Excel.Application.NETWORKDAYS(start_date,end_date)
0
Author Commented:
okay so all i've done .. is this ..

Function GetNumberOfWorkDays(sStartDate, sEndDate)

GetNumberofWorksDays = Excel.Application.NETWORKDAYS(sStartDate, sEndDate)

End Function

Have i done that wrong .. it tells me it's too complex to evaluate.!

thanks

Dave
0
Commented:
John,

as you may know, it is possible to buy Access as a single application, - similar to buying Excel or Word, and to the best of my knowledge you are not licensed to use the Word or Excel modules, unless you have bought the application.

If I'm wrong in that assumption, do feel free to correct me, - but please don't shout: I do have glasses..

Sven
0
Commented:
Hmmm........

can't seem to locate any function called NetWorkDays in my Excel module......

could it be another name?

trying WorkDays.....

nope, - no luck....

trying *workd* in all Office modules

nope....

Sorry, - I don't seem to have that function in my Office version, - could we be talking XP here, cause I'm using Office 2000 ?

Only hits on Office 97 ? And requires that you load the Analysis ToolPak ?

Seems like Microsoft has dropped the function in 2000 ?

Sven
0
Commented:
Hmmmmm...

perhaps the answer is found in the reference that was provided:
http://msdn.microsoft.com/library/en-us/dnacc2k2/html/odc_actips.asp

nope, - not a word about how to calculate working days, - actually the word 'work' isn't even on the page.....

Looks like DateDiff() has taken over after all the various application-specific add-ons, so I think I will maintain my recommendation of this function.

Kind regards,
Sven
0
Commented:
My apologies svenkarlsen, I uppercased as an emphasis not a shout. But please restrain yourself, the sore brand of sarcasm you are indulging, is frowned upon on this website!

Three points come to mind:
(1) butcherd11  has Excel
(2) just because something is not visible doesn't mean that it isn't there or can't be Set / Appended
(3) Microsoft issues knowledge based articles usually to support a frequent question. If they issue on a software release version they don't always reissue the same article for later versions. Sometimes they even miss out on adding the later version to the reference list.

butcherd11
Curiouser & curiouser. Excel '97, Excel 2000 & Excel XP all support Networkdays. The above surrogate application method works. I'm going to have a good look at this; in the meantime, make sure your Analysis Tool-Pak add-in in Excel is active. I'll get back quickly to you.
Regards,
John
0
Commented:
John,

if you feel that I have in any way insulted or intimidated you, I strongly suggest that you report that to the moderator.

> (2) just because something is not visible doesn't mean that it isn't there or can't be Set / Appended

When it's not in the libraries, it's not available. I enabled the Excel library and searched it with no luck, - I searched the Excel VBA help for the function with no luck, - as I have a full Office 2000 Premium installation it leads me to the conclusion that it is not readily available in the Microsoft Excel 9 Object Library

> (3) Microsoft issues knowledge based articles usually to support a frequent question. If they issue on a software release version they don't always reissue the same article for later versions. Sometimes they even miss out on adding the later version to the reference list.

I do not use Knowledge Base for identifying standard functions, I use the MSDN library which documents all Microsoft standard libraries. In this instance I included a search of the Knowledge Base to make certain.

Sven
0
Commented:
svenkarlsen
You'll never find it in Object Browser; it's an Add-In function!!!

Datediff doesn't work, as you believe, in its current form: "ww" counts calendar weeks and "w" counts rounded down data-to-date weeks not weekdays!

regards,
John
0
Commented:
butcherd11
If you want to use Datediff properly, then the following is a complete one line function to count weekday differences, no loops, no external references. It doesn't matter if the start date is later than the end date:

Function GetNumberOfWorkDays(sStartDate, sEndDate)

GetNumberOfWorkDays=Abs(5 - (Abs(Weekday(sStartDate, 7) - 2) + (Weekday(sStartDate, 7) - 2)) / 2 + (DateDiff("ww", sStartDate, sEndDate, 7) - 1) * 5 + (Abs(Weekday(sEndDate, 7) - 2) + (Weekday(sEndDate, 7) - 2)) / 2)

End Function

Regards,
John
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
butcherd11,
This Networkdays excel function is guaranteed to work.

####################################################
Function GetNumberOfWorkDays(sStartDate, sEndDate)

Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open (objExcel.Application.LibraryPath & "\Analysis\atpvbaen.xla")
objExcel.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)

GetNumberOfWorkDays = objExcel.Application.Run("atpvbaen.xla!NETWORKDAYS", sStartDate, sEndDate)

objExcel.Quit
Set objExcel = Nothing

End function

####################################################

The Excel Networkdays function, above, is inclusive of the start and end dates. My previous one line function does not count the start date (emphasis on 'between'). To modify the Networkdays function not to use the start date, replace the fifth line with:
GetNumberOfWorkDays = objExcel.Application.Run("atpvbaen.xla!NETWORKDAYS", sStartDate, sEndDate,namevalue(sStartDate))
Personally I think that the one line function in my previous posting is faster!!
Regards,
John

0
Commented:
butcherd11,
A small note... if you wish to include the start date in the count, replace sStartDate with sStartDate-1
Merely adding 1 to the answer would be erroneous if the start date was on a weekend.
regards,
John
0
Author Commented:
Thanks for that ... already tried it.
Works perfectly for me thank you. A Much appreciated result.

Regards
0
Commented:
svenkarlsen,
Some references you may find useful

Using Analysis ToolPak Functions In VBA

ACCESS 97 version at:
http://support.microsoft.com/default.aspx?kbid=153748

ACCESS 2000 version at:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;198571
Regards,
John
0
Commented:
You're welcome butcherd11,

Another small note:
(1) The entire function is encased in ABS() to give a positive result regardless of date order. If your date orders are correct, removing it may speed it up a small amount.
(2) Not wishing to be pedantic (never! :) ), according to the marking protocols, a correct solution deserves an 'A'. It doesn't take points from you. If you don't object, I'll ask the Moderator to upgrade.

Regards,
John
0
Author Commented:
Sorry... yes should have been .. possibly a slip of the keyboard.

0
Commented:
Grazias Senor!
0
Commented:
John,

I expect that we shall meet on other questions in the future, so why don't we just forget about our dialog on this page and start on a fresh next time we meet ?

Sven
0
Commented:
Sven,
I apologise for the pugilism and shouldn't have baited you. You got me on a bad day and I suppose I got a bit righteous when you advised using Steve's incorrect solution. You're worthy of respect and will receive it any time we meet in the future.
Best regards,
John
0
Commented:
John,
no need to apologize, - having a temper is being human, realizing it is being wise ;-)

You just improved my day, regards,

Sven
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.