how to omit wk ends and holidays

  • Thread starter Thread starter LG
  • Start date Start date
L

LG

I have a query that needs to find out how many days are between 2 dates.
Ex: TAT: [SUBMITTED]-[DMRDATERCV]. How would I apply to this to take out
wkends and any holidays that may occur with in the yr?
Thank you
 
You'd think that weekdays would be easy with the DateAdd function and "ww"
but it's not. However MS does have a function that you can use.

http://support.microsoft.com/kb/207795

Holidays become more of a problem. What country are you from? Even if you
are in the USA, different States celebrate different holidays. Even companies
can chose to follow different holidays.

You'll probably need a table of holidays and check it for holidays between
the dates. Something like the DCount function could return a number to
subtract.
 
Subject: Computing working days between two dates.



Compute the number of working days between two dates:

WorkDays=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",
[StartDate],[EndDate
],7)+DateDiff("ww",[StartDate],[EndDate],1))

[[[ watch out for word wrap !!!]]]

First datediff is total number of days between the dates.
Second datediff is number of Saturdays between the dates
Third datediff is number of Sundays between the dates.

Workdays is therefore equal to total number of days less the number of
Saturdays and less the number of Sundays.
You may then have to add 1 depending on how you want to count days -
if start is today and tomorrow is the end is that 1 or two days? If
start and end are the same date is that 0 days or 1 day. Usually you
will probably add one to the calculation.

The solution that Jerry suggests is the way that I do it for holidays.
Be sure that the dates in the tables are the "Work Days" that would
normally be counted (ie. specify the friday date that would be taken
off in place of the actual Saturday date of the holiday, etc.) Then be
sure that the user knows of and has a way of updating that table. The
user needs to be trained to become the owner of that aspect of the
application.

Ron
 
Back
Top