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
On Jan 22, 7:37=A0am, 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
The sat/sun part is not too difficult but you have to define what a
holiday is to accomplish the rest.
news:5a2e30bc-0604-409a-bc40-38f203785705@i12g2000prf.google groups.com...
On Jan 22, 7:37 am, 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
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.
On Jan 22, 7:37=A0am, 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
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 =3D "11/20/2007" ' You would provide the
start date
EndDate =3D StartDate
While Count1 < 20 ' 20 =3D Number of days to add
EndDate =3D DateAdd("d", 1, EndDate)
If DayOff(EndDate) =3D False Then
Count1 =3D Count1 + 1
End If
Wend
EndDate =3D 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 =3D FormatDateTime(Indate, vbLongDate)
If InStr(hstring, "Saturday") Then DayOff =3D True
If InStr(hstring, "Sunday") Then DayOff =3D True
If Month(Indate) =3D 7 And Day(Indate) =3D 4 Then Dayoff - True '
July 4th
If Month(Indate) =3D 12 And (Day(Indate) =3D 24 Or Day(Indate) =3D 25 Or
Day(Indate) =3D 31) Then DayOff =3D True ' Christmas Eve Christmas
Day NewYears Eve
If Month(Indate) =3D 1 And Day(Indate) =3D 1 Then DayOff =3D True '
NewYears Day
If Month(Indate) =3D 9 And InStr(hstring, "Monday") And Day(Indate) < 8
Then DayOff =3D 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) =3D 11 And InStr(hstring, "Thursday") And (Day(Indate)
> 21 And Day(Indate) < 29) Then DayOff =3D True ' Thanksgiving
' Thanksgiving is always the 4th Thursday of November. So if month =3D
11 and day =3D Thursday and day of month is greater than 21 and less
than 29 it is Thanksgiving.
If Month(Indate) =3D 11 And InStr(hstring, "Friday") And (Day(Indate) >
22 And Day(Indate) < 30) Then DayOff =3D True ' Friday after
Thanksgiving
End Function