I want to just count Business Days in a formula

lbc0056

New Member
Joined
Sep 2, 2014
Messages
8
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.
Date Wanted Date ReceivedDays Early/Late
3/24/20143/31/20147

<tbody>
</tbody>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Code:
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,207,392
Messages
6,078,223
Members
446,323
Latest member
fishezuk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top