# I want to just count Business Days in a formula

#### lbc0056

##### New Member
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 Received Days Early/Late 3/24/2014 3/31/2014 7

<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.
What version of Excel do you have? Check out the NETWORKDAYS.INTL function in Excel Help.

Code:
``````Function BUSINESSDAYS(sDate As Range, eDate As Range)

If sDate = 0 Or eDate = 0 Then
Exit Function
End If

If eDate.Value < sDate.Value Then
For x = eDate.Value To sDate.Value
Next x

'REMOVE THIS IF YOU DON'T WANT EARLY TO BE NEGATIVE
'REMOVE THIS IF YOU DON'T WANT EARLY TO BE NEGATIVE
Exit Function
End If

For x = sDate.Value To eDate.Value
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.

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

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

Replies
10
Views
448
Replies
4
Views
262
Replies
9
Views
359
Replies
4
Views
349
Replies
7
Views
434

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.

### Which adblocker are you using?

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

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