VBA Count days between dates excluding weekends and holidays

[Yodelayheewho]

I am new at this and I am able to get the total count of days working, but I can't seem to exclude weekends and holidays.
The requirement is to count the days it takes to process an order. If the order is completed on the same day the PO is received, we do not count that day.
In this examples below, the PO is received on Friday, May 26th.
05/26/2023 = PO Rec'd

The next several lines show examples of entry dates and the results I need.
05/26/2023 = 0 (Same Weekday)
05/27/2023 = 0 (Saturday)
05/28/2023 = 0 (Sunday)
05/29/2023 = 0 (Holiday)
05/30/2023 = 1 (Weekday)
05/31/2023 = 2 (Weekday)
06/01/2023 = 3 (Weekday)
06/02/2023 = 4 (Weekday)

The next several lines show examples of the results I'm getting from the code below.
05/26/2023 = 0 (Same Weekday)
05/27/2023 = 1 (Saturday)
05/28/2023 = 2 (Sunday)
05/29/2023 = 3 (Holiday)
05/30/2023 = 4 (Weekday)
05/31/2023 = 5 (Weekday)
06/01/2023 = 6 (Weekday)
06/02/2023 = 7 (Weekday)

Also if the PO is Rec'd, but the date the order is processed is blank (because it hasn't been processed yet), I want the cell to remain blank. Instead, when I save the data from the userform to the worksheet, I get a 5-digit negative number, i.e., -45072

Function ProcessDaysCount(x As Date, y As Date) As Long
Dim x As Date 'txtPORecDate
Dim y As Date 'txtSOAE10
Dim days As Long 'days between dates
Dim hol As Range 'list of holiday dates
x = DateValue.txtPORecDate
y = DateValue.txtSOAE10
days = x - y
hol = Application.Worksheets("LISTS").Range("S2:S")

If y = "" Then 'does not work, does not leave the cell blank
End If

Dim fullweek As Long 'identifies where the start and end date fall in a week
fullweek = days \ 7
Dim wkstart As Integer
Dim wkend As Integer
wkstart = Weekday(x)
wkend = Weekday(y)

Dim SatSun As Long 'does not work, weekend days are not subtracted
If days = (fullweek * 7) Then
    SatSun = 0
ElseIf wkstart = vbSunday And wkend = vbSaturday Then
    SatSun = 2
ElseIf wkstart = vbSunday Or wkend = vbSaturday Then
    SatSun = 1
Else
    SatSun = 0
End If

ProcessDaysCount = days - (fullweek * 2) - SatSun - hol

End Function

Your help would be greatly appreciated.

 

[p45cal]

I'm not near a computer at the moment and will be able to answer more specifically later, if someone else doesn't jump in. Check out application.networkdays_intl .

 

[p45cal]

hol = Application.Worksheets("LISTS").Range("S2:S")

Is this Google Sheets?!

Function ProcessDaysCount(x As Date, y As Date)
If y = 0 Or x = 0 Then
  zz = ""
Else
  zz = Application.NetworkDays_Intl(x, y, 1, Sheets("LISTS").Range("S2:S4")) - 1
End If
ProcessDaysCount = zz
End Function

Or ditch the vba function and use plain worksheet functions on the sheet:
=IF(B4="","",NETWORKDAYS.INTL($B$2,B4,1,$S$2:$S$3)-1)

 

[Yodelayheewho]

Hi p45cal,
This is not Google Sheets.
I tried a variation of your code and it worked, except it did not exclude weekends or holidays

Function ProcessDaysCount(x As Date, y As Date)
Dim x As Date 'txtPORecDate
Dim y As Date 'txtSOAE10
Dim days As Long 'days between dates
x = DateValue.txtPORecDate
y = DateValue.txtSOAE10
days = txtDays
If y = 0 Or x = 0 Then
days = ""
Else
days = Application.NetworkDays_Intl(x, y, 1, Sheets("LISTS").Range("S2:S")) - 1
End If
ProcessDaysCount = days
End Function

I tried the formula on the worksheet and it does work. However, I'm using a userform to enter the data on the worksheet. When I click on the command button on the userform to save my entry to the worksheet, it enters the value in the cell and wipes out the formula, which means I have to change my code. I really prefer to find a vba solution.

I greatly appreciate your help.

 

[p45cal]

My code works here:



but note that:



Range("S2:S")
is not a valid range in Excel, it's missing a number at the end. That's why i asked if this was Google Sheets because it looks like their nomenclature for open ended ranges.

There are so many things wrong with your variation of the code that I need to see the context fully. Can you link to a file with the bare minimum in to demonstrate it not working?

 

[Yodelayheewho]

I believe I figured it out. I place the following code under two subroutines: cmbUpdate_Click() and cmbAddNew_Click(). So, whether I add a new order (row) or update an existing order (row), the calculation works like a charm.

>>> use code - tags <<<

'***Calculates Days to Process Order***
With Worksheets("LISTS")
    Holidays = .Range(.Range("S2"), .Range("S1").End(xlDown)).Value
End With
txtDays = (Application.WorksheetFunction.NetWorkDays(txtPORecDate, txtSOAE10, Holidays)) - 1

I greatly appreciate your help!