Business/Workdays

[lee2k60]

Hi all,

I'm looking for some help regarding calculating business days in VBA. I have a function which is great, however I'm automating the input of the said formula and I can find no way of calling a user defined function in a sub routine. I've started with this;

Please Login or Register  to view this content.

It works great on some date ranges, but on others I can't understand where the value is coming from. For example;

Start Date = 16/10/10
End Date = 22/10/10

Business Days Function = 4 days
The above coding = 4 days

But then I get this;

Start Date = 17/10/10
End Date = 25/10/10

Business Days Function = 5 days
The above coding = 7 days

Clearly there is something missing from my code. Any help would be much appreciated.

Thanks,
Lee

[DonkeyOte]

Assuming you're looking to avoid dependence on Analysis ToolPak functionality (which is why you're not just using WORKDAY and NETWORKDAYS etc) then:

Please Login or Register  to view this content.

technically you don't need F & G to calc C (as illustrated) but left in nonetheless

[Richard Buttrey]

Hi Lee,

You are not deducting the 2 weekend days when updating the "C" & lx cell with the correct business days.

Create a new variable lWe as Long
Set this to 2 in the following code and deduct the lWe value from the Vusiness days calc and reset it to zero before the next iteration.

Please Login or Register  to view this content.

But why are you re-inventing the wheel? Excel already gives you the NETWORKDAYS() function to do exactly this. You don't need a macro, just use

Please Login or Register  to view this content.

and copy it down your range. If you really want to use a macro then use the
Application.WorksheetFunction.NetworkDays syntax

HTH

[lee2k60]

Thanks Richard & DonkeyOte.

All sorted now. Really appreciate your input.

Cheers,
Lee