Count Business Days

[Guest]

Is there a function similar to networkdays in Excel?

Basically count the number of business days between startdate and enddate.

Thank you,

David

 

[Graham R Seach]

David,

'Number of weekdays between two dates (by Doug Steele MVP)
myInt = DateDiff("d", dte1, dte2) - _
DateDiff("ww", dte1, dte2, 1) * 2 - _
IIf(Weekday(dte2, 1) = 7, _
IIf(Weekday(dte1, 1) = 7, 0, 1), _
IIf(Weekday(dte1, 1) = 7, -1, 0))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Guest]

Graham,
Not sure how it works, but it's perfect.

Thank you,
David

 

[Graham R Seach]

<<Not sure how it works, but it's perfect.>>
Call it "Steele magik".

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[D1Lundy]

I'd like to share my solution also.

I run a straight query type report that doesn't use any VB code. The report is automated to run hourly and has to calculate number of weekdays for each record and the report returns a few thousand. I really did not want to implement the code recommended here (nothing against vb code - I just didn't want to remake the whole report). I thought I should share how I solved this issue in case someone else is looking for a similar solution as I have not seen it here or on any other forums.

** Note: this does not account for holidays

I did some reading and the datediff "w" counts the number of weeks by counting the number of Sundays between the dates. You can also add an optional indicator to change what day the week starts on (so it then counts the number of Saturdays).

I used the datediff function to calculate the total number of days in one column. Sunday: Datediff("w",[startdate],[enddate]) in column 2 and Saturday: Datediff("w",[startdate],[enddate],7) in column 3.

Then all I had to do was filter it into a second query that had Weekdays: [days]-[Sunday]-[Saturday].

I've spot checked this for accuracy given a start date and end date of any day of the week and it works every time, no vb code required. IF you are looking to find the weekdays through queries only, this may help.