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:

Public Function WorkDays(dteStart, 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(dteStart + i, dowFirstDayOff) > lngDaysOffCount Then
            dteFirstDayOff = dteStart + i + 7 - Weekday(dteStart + 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
 

Regards,
Fish

[FishVal]

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