Workday Date Calculation (1 Viewer)

rbsterli

New member
Local time
Today, 10:12
Joined
Jan 5, 2000
Messages
2
I am trying to calculate a date that will be 5 WORKDAYS from a received date on my form. This date MUST EXCLUDE WEEKENDS AND HOLIDAYS. I have a separate table consisting of holidays for starters. Lotus 123 has a function called "@Workday" to perform this task. Anyone have any ideas?
 

Travis

Registered User.
Local time
Today, 03:12
Joined
Dec 17, 1999
Messages
1,332
I don't know of a simple Access function but I do have this code that might work for you:

Add it to an Access Module:

Public Function WorkingDays(dDate As Date, lInterval As Long) As Date
Dim x As Long ' Count of Working Days
Dim y As Long ' Count of actual Days
Dim dNewDate As Date ' The date being checked
Do Until x = lInterval
dNewDate = DateAdd("w", y + 1, dDate)
'1 = Sunday
'2 = Monday
'3 = Tuesday
'4 = Wednesday
'5 = Thursday
'6 = Friday
'7 = Saturday
If Weekday(dNewDate) > vbSunday And Weekday(dNewDate) < vbSaturday Then 'Using the Default of Sunday as firstday of week
'if dNewDate <> a holiday then (do your check here against your holiday table
x = x + 1
'End if
End If
y = y + 1
Loop
WorkingDays = DateAdd("w", y, dDate)

End Function

to call it just set it as the value of a field.

Me.[DeliveryDate] = WorkingDays([ShipDate],5)
if [ShipDate] = 1/5/00 then [DeliveryDate] will be five workdays from now or 1/12/00

Hope this helps
 

Users who are viewing this thread

Top Bottom