Guys

Im trying to work out the exact business days for two dates

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

2. In your post here you say

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 try this on a worksheet and it is consistently 1 day less than NETWORKDAYS which I believe is inclusive of start and end dates.

Perhaps something like this would suffice in the Master sheet module

Code:
```Sub WrkDaysPastTarget()

Dim a5 As Date
Dim a6 As Date
Dim ressfp As Double
Dim b As Long
Dim num_of_rows As Long

num_of_rows = Cells(Rows.Count, "O").End(xlUp).Row

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 = Application.WorksheetFunction.NetworkDays(a5, a6) - 1
'put the result somewhere
Worksheets("Master").Range("Q" & b).Value = ressfp
Next b

End Sub```
Hope this is of some assistance. Good Luck.

3. Hi Nos

Thanks that seems to to it except I had to take out the -1 to give the correct number of days if the end date is 1 second over a full day then it needs to round to the next day

I have one final problem

when reading the data into a5 and a6 is there a way to check if it is actually a date

this is because sometimes there is not a start date as it is not applicable and has "NA" in the cell

If i run the formula with this networks days formula it crashes so i need to check that both variables are in fact a date

Thanks

Mark

4. Can you define "it crashes"? What it does or doesn't do or what it tells you would be more useful.

I'm guessing it doesn't "crash", but rather indicates a type mismatch and doesn't run the macro.

try this

Code:
```For b = 2 To num_of_rows
If IsDate(Worksheets("Master").Range("O" & b).Value) Then
If IsDate(Worksheets("Master").Range("P" & b).Value) Then
a5 = Worksheets("Master").Range("O" & b).Value 'target comp date
a6 = Worksheets("Master").Range("P" & b).Value 'actual comp date
ressfp = Application.WorksheetFunction.NetworkDays(a5, a6)
'put the result somewhere
Worksheets("Master").Range("Q" & b).Value = ressfp
End If
End If
Next b```
I have to ask: This macro actually does nothing other than reduce your original formula from
=NETWORKDAYS(A2,B2)-(MOD(A2,1)>=MOD(B2,1))
to
=NETWORKDAYS(A2,B2)
so what were you trying to do with the MOD sections which gets the hours and minutes from the date? Are you aiming towards calculating working hours rather than working days?

5. Its OK Nos i used "isDate" to check if a5 and a6 are dates and it works fine

Mark

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•