Results 1 to 5 of 5

Thread: Business Days

  1. #1

    Business Days



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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 by NoS; 2013-08-25 at 12:43 AM.

  3. #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. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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. #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
  •