Computing number of days between 2 dates not counting the weekends.

[vspsdca]

Hi,
I'm quite new to the world of VBA so any help you could extend will help me a lot with my project.

I have a database of Change Requests from our users. I have two date boxes, one is Date Request Raised and Deadline for Entry. What I'd like to happen is when I enter the date on the Date Request Raised the Deadline for Entry would automatically be filled based on the Asset type that was selected.

For example:
if the asset type = 2 then the Deadline for Entry would only be a day from when the request was raised. If the asset type is <> 2 then the deadline for entry would be 5 working days from the day the request was raised where the weekend days are not counted.

I saw a module for just counting the weekdays but I just dont know where and how I can use the module with the DateAdd function for the situation i mentioned above.

If you can provide me with a solution and guide me as well as to where I should put the code in (i.e., Before Update, OnClick etc.), I'd really appreciate it.

Many thanks.

[FishVal]

Something like this:

[code=vb]
Public Function WorkDays(dteSta rt, dteEnd, _
Optional dowFirstDayOff As VbDayOfWeek = vbSaturday, _
Optional lngDaysOffCount As Long = 2) As Long

Dim lngDaysOff As Long
Dim dteFirstDayOff As Date

'calculate total days inclusively
WorkDays = DateDiff("d", dteStart, dteEnd) + 1


'subtract days off count moving [dteStart] forward [lngDaysOffCount] times
For i = 0 To lngDaysOffCount - 1

'find first day off date in the range
If Weekday(dteStar t + i, dowFirstDayOff) > lngDaysOffCount Then
dteFirstDayOff = dteStart + i + 7 - Weekday(dteStar t + i, dowFirstDayOff) + 1
Else
dteFirstDayOff = dteStart + i
End If

'exit in a case [dteFirstDayOff] got out of the range
If dteFirstDayOff + i > dteEnd Then Exit Function
'calculate [dteFirstDayOff] weekdays in the range and subtract from the total days
WorkDays = WorkDays - DateDiff("w", dteFirstDayOff + i, dteEnd) - 1

Next i

End Function
[/code]

Regards,
Fish

[FishVal]

Bugfix:
Line #23 "+i" has to be removed.