Solved

Working Days Calculator

Posted on 2003-11-25
30
12,648 Views
Last Modified: 2012-08-13
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
    sDay = WeekDay(DateAdd("d", i, sStartDate))
    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
0
Comment
Question by:butcherd11
  • 15
  • 8
  • 5
  • +1
30 Comments
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9817135
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
   sDay = WeekDay(DateAdd("d", i, sStartDate))
   If sDay <> 1 And sDay <> 7 Then
     iWorkDays = iWorkDays + 1
   End If
 Next
 GetNumberOfWorkDays = iWorkDays
End Function


Regards,
Sven
0
 
LVL 39

Expert Comment

by:stevbe
ID: 9817298
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
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9817327
(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
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9817356

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
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9817362
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
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9817412
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
 
LVL 1

Author Comment

by:butcherd11
ID: 9817631
Thanks to all comments and answers guys .. i'm looking at all possibilities at present... keep watching this space.

Thanks

Dave
0
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9817905
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
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9818093
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
 
LVL 1

Author Comment

by:butcherd11
ID: 9818257
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
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9818323
TRY IT IN THIS FORMAT
dblResult = Excel.Application.NETWORKDAYS(start_date,end_date)
0
 
LVL 1

Author Comment

by:butcherd11
ID: 9818856
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
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9819403
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
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9819533
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 ?

Hmmm - let's ask MSDN.....

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

Seems like Microsoft has dropped the function in 2000 ?


Sven
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9819587
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
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9823590
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
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9823859
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
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9824017
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
 
LVL 3

Accepted Solution

by:
jjjtuohy earned 250 total points
ID: 9824219
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
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9826209
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
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9831338
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
 
LVL 1

Author Comment

by:butcherd11
ID: 9831347
Thanks for that ... already tried it.
Works perfectly for me thank you. A Much appreciated result.

Regards
0
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9831407
svenkarlsen,
Some references you may find useful

Using Analysis ToolPak Functions In VBA
http://www.rb-ad.dircon.co.uk/rob/excelvba/tips/index.htm

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
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9831508
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
 
LVL 1

Author Comment

by:butcherd11
ID: 9831517
Sorry... yes should have been .. possibly a slip of the keyboard.

Please do ask to upgrade...
0
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9831767
Grazias Senor!
0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9832135
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
 
LVL 3

Expert Comment

by:jjjtuohy
ID: 9849242
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
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9849278
John,
no need to apologize, - having a temper is being human, realizing it is being wise ;-)

You just improved my day, regards,

Sven
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now