Workdays Function

[jamescsmith.128]

Evening,

I am having issues with this workdays funtion: I am trying to perform this funtion on multiple line items based on dates in certain locations. The issue is that not all items have made it as far as others. I am in need of help on how to make this funtion ignor blank date blocks yet still provide an output:

Option Compare Database
Option Explicit

Public Function workdays(ByRef startDate As Date, _
ByRef endDate As Date, _
Optional ByRef strHolidays As String = "Holidays" _
) As Integer
' Returns the number of workdays between startDate
' and endDate inclusive. Workdays excludes weekends and
' holidays. Optionally, pass this function the name of a table
' or query as the third argument. If you don't the default
' is "Holidays".
On Error GoTo Workdays_Error
Dim nWeekdays As Integer
Dim nHolidays As Integer
Dim strWhere As String

' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)

nWeekdays = WEEKDAYS(startDate, endDate)
If nWeekdays = -1 Then
workdays = -1
GoTo Workdays_Exit
End If

strWhere = "[Holiday] >= #" & startDate _
& "# AND [Holiday] <= #" & endDate & "#"

' Count the number of holidays.
nHolidays = DCount(Expr:="[Holiday]", _
Domain:=strHolidays, _
Criteria:=strWhere)

workdays = nWeekdays - nHolidays

Workdays_Exit:
Exit Function

Workdays_Error:
workdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Workdays"
Resume Workdays_Exit
End Function

Public Function WEEKDAYS(ByRef startDate As Date, _
ByRef endDate As Date _
) As Integer
' Returns the number of weekdays in the period from startDate
' to endDate inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Sunday and
' do not total two per week in number, this function will
' require modification.
On Error GoTo Weekdays_Error

' The number of weekend days per week.
Const ncNumberOfWeekendDays As Integer = 2

' The number of days inclusive.
Dim varDays As Variant

' The number of weekend days.
Dim varWeekendDays As Variant

' Temporary storage for datetime.
Dim dtmX As Date

' If the end date is earlier, swap the dates.
If endDate < startDate Then
dtmX = startDate
startDate = endDate
endDate = dtmX
End If

' Calculate the number of days inclusive (+ 1 is to add back startDate).
varDays = DateDiff(Interval:="d", _
date1:=startDate, _
date2:=endDate) + 1

' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=startDate, _
date2:=endDate) _
* ncNumberOfWeekendDays) _
+ IIf(DatePart(Interval:="w", _
Date:=startDate) = vbSunday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=endDate) = vbSaturday, 1, 0)

' Calculate the number of weekdays.
WEEKDAYS = (varDays - varWeekendDays)

Weekdays_Exit:
Exit Function

Weekdays_Error:
WEEKDAYS = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit

End Function

 

[pbaldy]

Re: Workdays Funtion

If you're saying that startDate or endDate may be Null, you'll need to replace them with whatever is appropriate to your needs. You'll also need to declare them as Variant rather than Date, as Date will error on a Null.

 

[jamescsmith.128]

Re: Workdays Funtion

Thank you so much. I knew it had to be something that would jump up and bite me.

I do have a follow-up question. I am getting a error code 94:invalid use of Null.

want to have the formula ignore all nulls and keep going. Is this possible. (I can replace the null, but that throws off my average functions)

 

[pbaldy]

Re: Workdays Funtion

Where? Did you change the variable declaration?

 

[jamescsmith.128]

Re: Workdays Funtion

i changed the startdate and enddate to variants. I get a runtime error 94 but all the old #errors change to 0.

 

[pbaldy]

Where do you get the error? You may need to temporarily comment out the "On Error..." lines.

 

[jamescsmith.128]

I feel so stupid... I rechecked my code. I completely forgot to take out my error message. Now it is removed an everything works perfect

 

[pbaldy]

Glad you got it sorted out.