Business Days

markbarr

New member
Joined
Aug 24, 2013
Messages
7
Reaction score
0
Points
0
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
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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.
 
Last edited:

markbarr

New member
Joined
Aug 24, 2013
Messages
7
Reaction score
0
Points
0
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
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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?
 

markbarr

New member
Joined
Aug 24, 2013
Messages
7
Reaction score
0
Points
0
Its OK Nos i used "isDate" to check if a5 and a6 are dates and it works fine

Mark
 
Top