Business Day Calculation

[markbarr]

Guys

I have the following formula in a cell

=NETWORKDAYS(A2,B2)-(MOD(A2,1)>=MOD(B2,1))

its works off of cells A2 for the start date and cell B2 for the end date and works perfect

I want to use this in code to automatically count business days for all rows. I have tried the following

Dim ressfp,a4,a5
a4 = Worksheets("Master").Range("O" & b).Value 'target comp date
a5 = Worksheets("Master").Range("P" & b).Value 'actual comp date
ressfp =NETWORKDAYS(a4,a5)-(MOD(a4,1)>=MOD(a5,1))

I get an error on the word MOD saying compile error syntax error but cant work out why

i have tried replacing the networkdays formula with the following

=SUM(INT((WEEKDAY(A2-{2,3,4,5,6})+INT(B2)-INT(A2))/7))-(MOD(A2,1)>MOD(B2,1))

but this gives the error that the curly brackets are invalid characters

Any ideas where im going wrong?

Mark

 

[Rick Rothstein]

Guys

I have the following formula in a cell

=NETWORKDAYS(A2,B2)-(MOD(A2,1)>=MOD(B2,1))

its works off of cells A2 for the start date and cell B2 for the end date and works perfect

I want to use this in code to automatically count business days for all rows. I have tried the following

Dim ressfp,a4,a5
a4 = Worksheets("Master").Range("O" & b).Value 'target comp date
a5 = Worksheets("Master").Range("P" & b).Value 'actual comp date
ressfp =NETWORKDAYS(a4,a5)-(MOD(a4,1)>=MOD(a5,1))

I get an error on the word MOD saying compile error syntax error but cant work out why

i have tried replacing the networkdays formula with the following

=SUM(INT((WEEKDAY(A2-{2,3,4,5,6})+INT(B2)-INT(A2))/7))-(MOD(A2,1)>MOD(B2,1))

but this gives the error that the curly brackets are invalid characters

Any ideas where im going wrong?

You are going wrong in assuming that VB has functions and constructs that are completely identical to those on a worksheet... it doesn't. You cannot establish arrays using curly brackets and VB does not have a MOD function... it has a Mod operator and this operator only works with whole number arguments and only returns whole number values, so you cannot find the fractional part of a number by using a modulus of 1. You should be able to use the Application's Evaluate function to force VB to let Excel do the calculation. Something like this should work (untested) for the line I highlighted above...

ressfp =Evaluate("NETWORKDAYS(" & a4 & "," & a5 & ")-(MOD(" & a4 & ",1)>=MOD(" & a5 & ",1))")

 

[markbarr]

Hi Rick

Unfortunately I cant get it to work

I have used the following code

Dim a5, a6, ressfp, b
For b = 2 To num_of_rows
a5 = Worksheets("Master").Range("O" & b).Value 'target comp date
a6 = Worksheets("Master").Range("P" & b).Value 'actual comp date
ressfp = Evaluate("NETWORKDAYS(" & a5 & "," & a6 & ")-(MOD(" & a5 & ",1)>=MOD(" & a6 & ",1))")
Next b

when run it crashes with the error "Type Mismatch"

when i hit debug and check the variables the following shows

a5= "08/07/2013 08:30:03"
a6= "23/07/2013 11:00:00"
ressfp="Error 2015"

I think its something to do with having the time there as well but that is needed as in this case the number of business days is 11 days 2 hours and 29 minutes and 57 seconds so the answer I need is "12"

Any Ideas?

Mark