Calculating workays between two dates

[hotplate]

Hi,
I am using the first function on this website:
http://www.mvps.org/access/datetime/date0006.htm
Is there a way to keep from getting #error if one of the 2 dates are
missing? The user may fill in the closing date later.

Thanks,
James

 

[Douglas J. Steele]

Change the parameters to Variants, rather than Dates, and check for Null
values in the code. I assume you want to return a 0 if they don't supply
both dates:

Public Function WorkingDays( _
StartDate As Variant, _
EndDate As Variant _
) As Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer

If IsNull(StartDate) Or _
IsNull(EndDate) Then
WorkingDays = 0
Else
StartDate = StartDate + 1

intCount = 0
Do While StartDate <= EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount
End If

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function