# Workday Date Calculation (1 Viewer)

#### rbsterli

##### New member
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.
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

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

Replies
6
Views
154
Replies
16
Views
187
Replies
3
Views
104
Replies
3
Views
174
Replies
12
Views
210