I want to just count Business Days in a formula

[lbc0056]

I am tracking how many days an order is late. The formula I am using is =IF(H4=0," ",H4-G4). That translate to =IF("Date Received"=0," ","Date Received"-"Date Wanted". Can I make it to where it only counts business days? We are open Monday through Thursday. [TABLE="width: 500"]
<tbody>[TR]
[TD]Date Wanted [/TD]
[TD]Date Received[/TD]
[TD]Days Early/Late[/TD]
[/TR]
[TR]
[TD]3/24/2014[/TD]
[TD]3/31/2014[/TD]
[TD]7 [/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

What version of Excel do you have? Check out the NETWORKDAYS.INTL function in Excel Help.

 

[NeonRedSharpie]

Function BUSINESSDAYS(sDate As Range, eDate As Range)

    If sDate = 0 Or eDate = 0 Then
        BUSINESSDAYS = ""
        Exit Function
    End If
    
    If eDate.Value < sDate.Value Then
        For x = eDate.Value To sDate.Value
            If Weekday(x, vbMonday) < 5 Then BUSINESSDAYS = BUSINESSDAYS + 1
        Next x
        
        'REMOVE THIS IF YOU DON'T WANT EARLY TO BE NEGATIVE
            BUSINESSDAYS = BUSINESSDAYS * -1
        'REMOVE THIS IF YOU DON'T WANT EARLY TO BE NEGATIVE
        Exit Function
    End If
    
    For x = sDate.Value To eDate.Value
        If Weekday(x, vbMonday) < 5 Then BUSINESSDAYS = BUSINESSDAYS + 1
    Next x


End Function

Here's a UDF that will do that. I couldn't think of a way that wasn't a huge nested if statement since you have a three day weekend.

 

[barry houdini]

As Andrew says you can use NETWORKDAYS.INTL function to count Monday to Thursday days, e.g.

=IF(H4=0,"",NETWORKDAYS.INTL(G4,H4,"0000111"))

the "0000111" indicates which days to count, starting with Monday - zero is included 1 is not

NETWORKDAYS.INTL is only available in Excel 2010 or later - in earlier versions you can use this version:

=IF(H4=0,"",SUM(INT((WEEKDAY(G4-{2,3,4,5})+H4-G4)/7)))

Note that both of these formulas count both start and end date, so Monday to Wednesday counts 3 days, if you want that to be 2 then you need to adjust the formula. Subtracting 1 is fine if start and end dates will always be on working days

 

[lbc0056]

Thank you! I had to adjust the formula a little bit, but this got me on the right track.