Date Evaluation

[dave rivera]

I am attempting to evaluate the number of days it takes to complete tasks.
Results can be same date for both start and end dates or end date greater
than start date by x days, taking into consideration weekends.
Ex.
Start Date End Date # Biz Days
10/20 10/20 0
10/20 10/21 1
10/17 10/20 1
10/20 10/22 2

 

[Dale Fye]

Write yourself a little function. Pass it the values of the start and end
date and then check the dates between them to see whether they fall on a
weekend.

Public Function Workdays(StartDate As Variant, EndDate As Variant) As Variant

Dim dtDate As Date
Dim intCount As Integer

'Accepts variants to allow Nulls
If IsNull(StartDate) Or IsNull(EndDate) Then
Workdays = Null
Exit Function
End If

intCount = 0
For dtDate = StartDate To EndDate
If Weekday(dtDate, vbMonday) < 6 Then
intCount = intCount + 1
End If
Next
Workdays = intCount - 1
End Function

You could even modify this to check for holidays, if you had a table where
you stored holidays.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.