Count Business Days Only ? (1 Viewer)

[dmdstar]

How on earth do we count ONLY business days... I have a function subtracting one date from another to get the difference, however, we need to EXCLUDE Saturday and Sunday. Please help!

Thank you.

 

[llkhoutx]

What about holidays! They're not business days.

I've seen this calculation, but don't recall it.

I'd testing each date in my date range and increment a counter accordingly. Thiss will be very fast.

Date1 = start date
Date2 = end date
' not that these are pure dates, with no time component (use CVDate if it has time component)
dim ij as integer
dim iDate as date
ij=0
For iDate = Date1 to Date2
if weeday(idate) = 1 or weekday(idate) = 7 then ij = ij + 1
next ij

ij results in the number of nonweekdays

If you need to check for holidays, also check each date with Dlookup to see if it's in the holiday table. Don't double increment ij!

 

[dmdstar]

Oh good gracious... I hadn't even THOUGHT of holidays! I think that can go by the wayside. I'll give this code a shot, and repost if I have trouble.

 

[Chris RR]

There's also Dev Ashish's excellent Access web site. His version is at: http://www.mvps.org/access/datetime/date0006.htm

I agree, you'll need to look up holidays if they are important to your results...

 

[dmdstar]

These are good suggestions, and shall be used... now my dillemma is, how do I incorporate this with a field in a report? I have a field that is getting the total number of days by subtracting the last from the first... now I need to only show the total BIZ days in this field, and am at an impass... I've heard that it's possible for a report to call code like this, however, after pouring through all my books, I have been unable to find how.

Thank you for any help you can provide!

 

[Chris RR]

One way to do it is to set up a function that does the whole business days calculation for you, including holidays and whatever. Then you can use that function in your report just as if it was an Access-provided function (they even show up in the Expression Builder, under "Functions" and your database name). You store the code in an Access module. Look up the "Function" statement in Access help.

(Another alternative would be to put the code directly into the report. But once you get this built, you may want to use it in other places in this database and even copy into other databases, so I'd keep it separate.)

 

[DH]

Going back to the function,
This link should have a function at the end of it which will remove all weekends & holidays.
http://www.athree.com

use the search facilty in the Access97 forum & look for Networkdays in the last 3 months and copy the attachment.

HTH

David

[This message has been edited by DH (edited 03-12-2001).]

 

[dmdstar]

It works wonderfully! I just wanted to post that if someone uses this, the very last line of the code should be changed to
funWorkDaysDifference = (lngTotalDays - lngTotalHolidays) - 1

add the "-1" to it because it was found to be counting the very first day as well, thus 1/20/01-1/20/01 would equal 1 day... when it should be zero.

That's it... Thanks for your help all!