You should be able to shorten the function considerably. The following is
adapted from one I put together some years ago:
Public Function Work( _
EndDate As Variant, _
StartDate As Variant, _
Optional strDayType As String = "Work") As Variant
Dim lngDaysDiff As Long, lngWeekendDays As Long
Dim intPubHols As Integer
If IsNull(EndDate) Or IsNull(StartDate) Then
Exit Function
End If
If strDayType <> "Work" Then
Work = DateDiff("d", StartDate, EndDate)
Else
' if first date is Sat or Sun start on following Monday
Select Case Weekday(StartDate, vbMonday)
Case vbSaturday
StartDate = StartDate + 2
Case vbSunday
StartDate = StartDate + 1
End Select
' if last date is Sat or Sun finish on following Monday
Select Case Weekday(EndDate, vbMonday)
Case vbSaturday
EndDate = EndDate + 2
Case vbSunday
EndDate = EndDate + 1
End Select
' get total date difference in days
lngDaysDiff = DateDiff("d", StartDate, EndDate)
' get date difference in weeks and multiply by 2
' to get number of weekend days
lngWeekendDays = DateDiff("ww", StartDate, EndDate, vbMonday) * 2
' subtract number of weekend days from total date difference
' to return number of working days
Work = lngDaysDiff - lngWeekendDays
End If
End Function
By way of explanation:
1. The function and the date parameters are declared as variants. This
would allow for the function to be called in a query for instance where some
rows might not have values in one or both of the date columns in the table.
This might explain your error as your function expects the dates as strings,
which can't be Null, though I'd have thought you'd get an 'invalid use of
Null' error in that case. With the above the function expects variants,
which can be Null, and would return Null if either or both dates are Null.
2. The DayType parameter is made optional and given a default value of
"Work". This means that if this is omitted the function would automatically
count only weekdays. If any other value is passed into the function it would
count all days. So:
Work(#05/03/2007#,#04/03/2007#) returns 22
whereas:
Work(#05/03/2007#,#04/03/2007#, "Calendar") returns 30
3. I hope that the comments in the code will be sufficient to explain how
it calculates the number of days. Basically, if counting work days, it first
makes sure that the count starts and ends with a weekday and gets the total
date difference; then it works out the number of weekends involved by
getting the difference in weeks, and multiplies by 2 to get the number of
weekend days, which are subtracted from the total date difference to give the
date difference in weekdays only.
4. It does not take account of public holidays. In fact the original
function on which the above is based has the option to do this or not, the
public holidays being stored in a table. It also has a Country parameter so
that it can use different public holidays for different countries. Here the
four constituent countries of the UK, along with the Republic of Ireland,
each have different public holidays, and as more countries are becoming
members of the European Union there can also be a more regular need to take
account of differences throughout Europe.
Ken Sheridan
Stafford, England
"Melissa" wrote:
> Ok I'm new at writing functions so be patient with me...I created this
> function but when I try to run a query on the data it brings up is gives me a
> data mismatch...Any suggestions?
>
> Public Function Work(EndDate As String, StartDate As String, DayType As
> String)
> Dim EndDate1, StartDate1 As Date
>
> EndDate1 = CDate(EndDate)
> StartDate1 = CDate(StartDate)
>
> Work = 0
>
> If DayType = "Calendar" Then
> Work = Abs(DateDiff("d", EndDate1, StartDate1))
> Else
> If Weekday(StartDate1) = 2 Then
> If Weekday(StartDate1) = Weekday(EndDate1) Then 'Monday
> Work = Abs(DateDiff("w", EndDate1, StartDate1)) * 5
> End If
>
> If Weekday(EndDate1) = 3 Then 'Tuesday
> Work = Abs(DateDiff("w", EndDate1, StartDate1)) * 5 + 1
> End If
>
> If Weekday(EndDate1) = 4 Then 'Wednesday
> Work = Abs(DateDiff("w", EndDate1, StartDate1)) * 5 + 2
> End If
>
> If Weekday(EndDate1) = 5 Then 'Thursday
> Work = Abs(DateDiff("w", EndDate1, StartDate1)) * 5 + 3
> End If
>
> If Weekday(EndDate1) = 6 Then 'Friday
> Work = Abs(DateDiff("w", EndDate1, StartDate1)) * 5 + 4
> End If
> End If
> etc etc
Similar Threads
