By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,484 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,484 IT Pros & Developers. It's quick & easy.

Date Fields

P: n/a
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
Jan 22 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Jan 22, 7:37*am, Paul <burnsp...@hotmail.co.ukwrote:
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.
Jan 22 '08 #2

P: n/a

<fr********@yahoo.comwrote in message
news:5a**********************************@i12g2000 prf.googlegroups.com...
On Jan 22, 7:37 am, Paul <burnsp...@hotmail.co.ukwrote:
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.

Jan 23 '08 #3

P: n/a
On Jan 22, 7:37*am, Paul <burnsp...@hotmail.co.ukwrote:
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

Jan 29 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.