Weekday woes

[hgus393]

Hi all,
I am racking my brain trying to solve this problem that I am having. I have a report that needs to be populated with 30 working days from a start date.

I have tried using this bit of code in a query but it does not work, ie it does not identify Saturdays and Sundays accordingly. can anyone see what I am doing wrong?

Function WEEKDATUM(startD As Date, q As Integer)
  '
    Dim RappDatum As Date
 
        If Weekday(startD + q) = 1 Then
        RappDatum = q + startD + 1
        Else: RappDatum = q + startD
        End If
        If Weekday(startD + q) = 6 Then
        RappDatum = q + startD + 2
        Else: RappDatum = q + startD
        End If
 
    WEEKDATUM = RappDatum
 
End Function

Bob

 

[namliam]

30 working days = 6 weeks, have you tried doing...
Yourdate + 30 / 5 * 7

Thusly converting weekdays into calander days... circumventing the issue.

This doenst take into account any national holidays or anything but does what you want it to in an easy maner.

Now if yourdate can be a saturday or sunday... You can use an if like:
If Weekday(date+1,vbsaturday) = 1 then
' do something to handle the saturday (perhaps add 2 days to land on the next monday)
elseif Weekday(date+1,vbSunday) = 1 then
' Add 1 day?
endif

If you want to take into account national holidays as well I suggest you search the forum in the Sample part. Couple of good ones down there.

Good luck!