to add a # of days... not including holidays/weekends

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

Hi,

I created a database.. in which in the table is a field
called CaseDate.

I have a query that queries this database and spits back
some other fields and including the CaseDate.. but I also
added to this query a 20Day and a 30 Day field.

Which basically takes the CaseDate and adds 20days to it..
and the 30Days field.. takes the same CaseDate and adds
30days to it.

Is this possible:
To do what I've done but...
when adding the 20days or 30days , can i have it not
include weekends.. and holidays ?

Any help is greatly appreciated.
Thanks
Aaron
(e-mail address removed)
 
Hi Aaron

I'm not quite sure as to what you're asking. What date range are you looking
for?

If you want the working days between 2 dates then the formula would be as
follows:

Best regards

Maurice St-Cyr

Micro Systems Consultants, Inc.

Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
 
Aaron said:
Hi,

I created a database.. in which in the table is a field
called CaseDate.

I have a query that queries this database and spits back
some other fields and including the CaseDate.. but I also
added to this query a 20Day and a 30 Day field.
Click to expand...

The 20Day and 30Day columns are repeating columns. They go against First
Normal Form, and create SQL coding challenges just by existing in the table.
I'd recommend deleting them because their values can always be calculated
dynamically when needed from CaseDate.
Which basically takes the CaseDate and adds 20days to it..
and the 30Days field.. takes the same CaseDate and adds
30days to it.

Is this possible:
To do what I've done but...
when adding the 20days or 30days , can i have it not
include weekends.. and holidays ?
Click to expand...

Whether the repeating columns are retained or not:

It looks like you need basic date calculation expressions to work from
when writing SQL and VBA code.

There are some interesting ideas at:

http://www.mvps.org/access/datetime/index.html

The following (a link under the above page), mentions that this routine
will accept an array of holidays. Maybe it will work for your purposes.

http://www.mvps.org/access/datetime/date0012.htm

And if I can be permitted to refer also to (speficially, rule #1):

http://www.mvps.org/access/tencommandments.htm



Oh, and thank you, Access Web authors, I use this stuff myself . . .
 
Back
Top