Calculating dates excluding weekends/holidays?

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I am trying to figure out how to make a query that will
add a certain number of days to a date but not to include
weekends or holidays. Is it possible to do that in
Access? If so can someone tell me how to do it?
 
Do a search. This has been asked and answered many many many times in these
groups.

Rick B

I am trying to figure out how to make a query that will
add a certain number of days to a date but not to include
weekends or holidays. Is it possible to do that in
Access? If so can someone tell me how to do it?
 
I have search and can't find what I am looking for. What
I am trying to do is: if I enter a date such as 7/22/04 I
want to say 5 days to that but to exclude weekend and
holidays, I can get it to add the 5 days which would be
7/27/04 but it includes the weekend. The actual date that
I want return would be 7/29/04. How do I go about making
that happen. And instead of telling me to search for it
(because I have) can someone tell me how it accomplish
this?
 
Mark,
I don't know the answer to you question, but you seem to have figured out how to do what I need to do.
I need to add three months to a date in one field. e.g. if I have a part returned to me on 7-25-04 and I do not want to reuse it for 3 months, how do I make this happen? Thanks
 
The code below is couresy of Bill Mosc, I have used it many times.
When you call the function specify the holidays or alternatley have a
separate table of holidays and loop hrough.


Function NetWorkDays(StartDate As Date, EndDate As Date, _
Optional Holiday1 As Date, Optional Holiday2 As Date) As Long
'Purpose: Determine the number of workdays between 2 dates.
' Note: be sure to use weekdays for both start and end dates.
Dim lngDays As Long

If StartDate = EndDate Then
NetWorkDays = 0
GoTo exit_NetWorkDays
End If

lngDays = EndDate - StartDate + 1 - Int((EndDate - StartDate + 1)
/ 7) * 2 - _
IIf(Int((EndDate - StartDate + 1) / 7) = (EndDate - _
StartDate + 1) / 7, 0, IIf(Weekday(EndDate) <
Weekday(StartDate), 2, 0)) - _
IIf(Weekday(StartDate) = 1 Or Weekday(EndDate) = 7, 1, 0)
If IsMissing(Holiday1) = False Then
If Holiday1 <> vbSaturday And Holiday1 <> vbSunday Then
lngDays = lngDays - 1
End If
End If
NetWorkDays = lngDays
Debug.Print NetWorkDays

exit_NetWorkDays:
Exit Function

End Function
 
A search on Workdays may well get better results for you if the code
below is not the answer you need
 
Brock,

The way I found to add to a date field is to use the
DateAdd function.
For example in your case you would use
DateAdd ("m",3,date)

Whereas the first part "m" is for months, you could
use "d" for days, "ww" for weeks. The second part (the
number)is the length, whether it is days, weeks or
monthes. The third would be the input date.
-----Original Message-----
Mark,
I don't know the answer to you question, but you seem to
Click to expand...
have figured out how to do what I need to do.
I need to add three months to a date in one field. e.g.
Click to expand...
if I have a part returned to me on 7-25-04 and I do not
want to reuse it for 3 months, how do I make this
happen? Thanks
 
There's no "magic bullet" for holidays (that I know of),
you'll have to create a table where you
enter holidays that won't count, for example,

table "Holidays" like:

ID HolDate HolName
1 9/6/2004 Labor Day
2 10/11/2004 Columbus Day
3 11/11/2004 Veterans Day
4 11/25/2004 Thanksgiving

Public Function fAddBusinessDay(pStart As Date, pAdd As Integer)
'Adds the proper Business day skipping holidays and weekends
'adapted from code by Arvin Meyer 05/26/98
On Error GoTo Err_fAddBusinessDay

Do While pAdd > 0
pStart = pStart + 1
If Weekday(pStart) <> 1 And Weekday(pStart) <> 7 Then
'not Sunday nor Saturday
If DCount("*", "Holidays", "[HolDate]=#" & pStart & "#") = 0 Then
'not holiday
pAdd = pAdd - 1
End If
End If
Loop

fAddBusinessDay = pStart

Exit_fAddBusinessDay:
Exit Function

Err_fAddBusinessDay:
MsgBox Err.Description
Resume Exit_fAddBusinessDay
End Function

?fAddBusinessDay(#11/24/04#,4)
12/1/2004
?fAddBusinessDay(#9/1/04#,15)
9/23/2004
?fAddBusinessDay(#7/22/04#,5)
7/29/2004
 
Back
Top