Calculated Time Open Field

[Roger Burns]

Hi

I have an Access 97 database that is to be used as a call
logging tool for an IT Helpdesk. One specific field that
I am having difficulty with is a Time Open field which
will be a calculated field to show the total length of
time a call was/is open for.

Currently this is set up as follows:
When a call is opened a field called 'Job Opened' is
date/time stamped using Now() function
When a call is closed a field called 'Job Closed' is
date/time stamped using Now() function
Time Open is then calculated in two parts - firstly using
DateDiff function to calculate number of days between date
job opened and closed. Secondly using a simple calculation
to find how many hh:mm:ss it was open for.

Although this is working fine it does not take account of
weekends or bank holidays and this, I'm told, is a
specific requirement of the database. So, can anyone
offer any advice how best to calculate the Time Open field
to show how long a call was open for, but taking both
weekends and bank holidays into account.

Any help is much appreciated as this is beginning to give
me a headache!!

Thanks

Roger

 

[SFAxess]

Holidays are a pain because they change from year to year
and business to business. Regardless of the holidays, you
are going to need to account for weekends, so check out
these procs at the Access MVPs site which will help you
count workdays only:
http://www.mvps.org/access/datetime/date0012.htm

Personally, I have a table that lists holidays for the
next few years, the code given at mvps.org passes an
array of holidays. I suggest making a table of work days
for a given period (i.e. 1950-2040). You can deal with
holidays on a function by function basis or use that
table and the holiday table to create another table which
excludes the holidays, thus you end up with a definitive
source for dates which are true workdays.
Unfortunately, this all has probably given you an even
greater headache, but thus is the nature of time
calculations.