Date Fields

[Paul]

Thanks to all the advice I have received but I seriously need more
help (in more ways than one).

The crux of the problem is that I need to insert a date into
ClockStarts field and have access display another date into TargetDate
field (plus 20 days but excluding Sat/Sun and holidays)

If someone could give me the code to do this I would be forever in
your debt. Thanks

[frogsteaks]

On Jan 22, 7:37*am, Paul <[e-mail]:

Thanks to all the advice I have received but I seriously need more
help (in more ways than one).

The crux of the problem is that I need to insert a date into
ClockStarts field and have access display another date into TargetDate
field (plus 20 days but excluding Sat/Sun and holidays)

If someone could give me the code to do this I would be forever in
your debt. Thanks

The sat/sun part is not too difficult but you have to define what a
holiday is to accomplish the rest.

[Larry Linson]

<fr********@yahoo.comwrote in message
news:5a**********************************@i12g2000 prf.googlegroups.com...
On Jan 22, 7:37 am, Paul <[e-mail]:

Thanks to all the advice I have received but I seriously need more
help (in more ways than one).

The crux of the problem is that I need to insert a date into
ClockStarts field and have access display another date into TargetDate
field (plus 20 days but excluding Sat/Sun and holidays)

If someone could give me the code to do this I would be forever in
your debt. Thanks

The sat/sun part is not too difficult but you have to define what a
holiday is to accomplish the rest.

Normal practice is to maintain a table of holidays and look them up.

[chris]

On Jan 22, 7:37*am, Paul <[e-mail]:

Thanks to all the advice I have received but I seriously need more
help (in more ways than one).

The crux of the problem is that I need to insert a date into
ClockStarts field and have access display another date into TargetDate
field (plus 20 days but excluding Sat/Sun and holidays)

If someone could give me the code to do this I would be forever in
your debt. Thanks

Try this it works for me. I cannot include weekends or holidays to
calculate days
late on an account. So I created the following function called
"DayOff"

Private Sub CBStart_Click()
Dim Count1 As Integer
Dim StartDate As Date, EndDate As Date
StartDate = "11/20/2007" ' You would provide the
start date
EndDate = StartDate
While Count1 < 20 ' 20 = Number of days to add
EndDate = DateAdd("d", 1, EndDate)
If DayOff(EndDate) = False Then
Count1 = Count1 + 1
End If
Wend
EndDate = Format(EndDate, "mm/dd/yyyy")
MsgBox str(EndDate)
End Sub

Function DayOff(Indate As Date) As Boolean
' This function converts any day to the Long Day format which contains
The Name of the day of the week
' If the Name of the day of the week is either Friday or Saturday then
the function is true
' I've given some examples of some holidays but you must add your own
if they are differend
Dim hstring As String
hstring = FormatDateTime(Indate, vbLongDate)
If InStr(hstring, "Saturday") Then DayOff = True
If InStr(hstring, "Sunday") Then DayOff = True
If Month(Indate) = 7 And Day(Indate) = 4 Then Dayoff - True '
July 4th
If Month(Indate) = 12 And (Day(Indate) = 24 Or Day(Indate) = 25 Or
Day(Indate) = 31) Then DayOff = True ' Christmas Eve Christmas
Day NewYears Eve
If Month(Indate) = 1 And Day(Indate) = 1 Then DayOff = True '
NewYears Day
If Month(Indate) = 9 And InStr(hstring, "Monday") And Day(Indate) < 8
Then DayOff = True ' LaborDay
' Laborday is always the first Monday of September. if the month is
9 and the day is Monday and the Day of the Month is less than 8 then
it is laborday
If Month(Indate) = 11 And InStr(hstring, "Thursday") And (Day(Indate)

21 And Day(Indate) < 29) Then DayOff = True ' Thanksgiving

' Thanksgiving is always the 4th Thursday of November. So if month =
11 and day = Thursday and day of month is greater than 21 and less
than 29 it is Thanksgiving.
If Month(Indate) = 11 And InStr(hstring, "Friday") And (Day(Indate) >
22 And Day(Indate) < 30) Then DayOff = True ' Friday after
Thanksgiving
End Function