I have a form that defaults to todays date as a received. I need the form then to calculate a date 5 business days later as a due date. I can make it auto-fill a date 5 days in the future, but cannot make it skip the weekends. Thanks.
Form that calculates dates (1 Viewer)
- Thread starter ALF3911
- Start date
Fornatian
Dim Person
- Local time
- Today, 06:12
- Joined
- Sep 1, 2000
- Messages
- 1,396
try this:
(acquired from DH, thanks!)
Function TargetWorkDate(dtStartDay As Date, intdays As Integer) As Long
'Function designed by Brian Maddocks Feb 2001 borrowing ideas from
'ThomRose 's workdays function
'ThomRose specified in that that permission to use is granted as long as you
'acknowledge the author
'This function calculates a target workdate given a start date, a number of
'days to add on and a tables of holiday dates
'It mimics Excel's workday function but does NOT reject start dates that are
'themselves HoliDays Or weekends
'There must be a table present called tblHolidays which contains the field
'dtObservedDate in the format date/time
'The table must list the dates of holidays to be observed.
'The user may include other fields in that table, for example, a description of
'the holiday being observed.
'This function should work with both US and UK date settings
Dim dtEndDay As Date
Dim dtinterimday As Date
Dim intcount As Integer
Dim Boolhol As Boolean
Dim lnginterimdate As Long
dtinterimday = dtStartDay
Do Until intcount = Abs(intdays)
If intdays > 0 Then dtinterimday = dtinterimday + 1 Else dtinterimday = dtinterimday - 1
lnginterimdate = dtinterimday
If WeekDay(dtinterimday, 2) <> 6 And WeekDay(dtinterimday, 2) <> 7 Then
Boolhol = DCount("dtObservedDate", "tblHolidays", "dtObservedDate = " & lnginterimdate)
If Boolhol = False Then
intcount = intcount + 1
End If
End If
Loop
TargetWorkDate = dtinterimday
End Function
____________________________________________
So to use it simply say:
My5DaysAheadField = Function TargetWorkDate(Date, 5)
HTH
Ian
[This message has been edited by Fornatian (edited 06-05-2001).]
(acquired from DH, thanks!)
Function TargetWorkDate(dtStartDay As Date, intdays As Integer) As Long
'Function designed by Brian Maddocks Feb 2001 borrowing ideas from
'ThomRose 's workdays function
'ThomRose specified in that that permission to use is granted as long as you
'acknowledge the author
'This function calculates a target workdate given a start date, a number of
'days to add on and a tables of holiday dates
'It mimics Excel's workday function but does NOT reject start dates that are
'themselves HoliDays Or weekends
'There must be a table present called tblHolidays which contains the field
'dtObservedDate in the format date/time
'The table must list the dates of holidays to be observed.
'The user may include other fields in that table, for example, a description of
'the holiday being observed.
'This function should work with both US and UK date settings
Dim dtEndDay As Date
Dim dtinterimday As Date
Dim intcount As Integer
Dim Boolhol As Boolean
Dim lnginterimdate As Long
dtinterimday = dtStartDay
Do Until intcount = Abs(intdays)
If intdays > 0 Then dtinterimday = dtinterimday + 1 Else dtinterimday = dtinterimday - 1
lnginterimdate = dtinterimday
If WeekDay(dtinterimday, 2) <> 6 And WeekDay(dtinterimday, 2) <> 7 Then
Boolhol = DCount("dtObservedDate", "tblHolidays", "dtObservedDate = " & lnginterimdate)
If Boolhol = False Then
intcount = intcount + 1
End If
End If
Loop
TargetWorkDate = dtinterimday
End Function
____________________________________________
So to use it simply say:
My5DaysAheadField = Function TargetWorkDate(Date, 5)
HTH
Ian
[This message has been edited by Fornatian (edited 06-05-2001).]
