Date and Time Question

[Dwight]

I am working with an Access database with dates and times. I have been using
the DATEDIFF Function in a SQL statement to get the difference between 2
dates/times. This has been working fine.

Recently it was asked of me to look at the difference between the 2 dates
and excluding the times that the business is not in operation. I am not
having much luck and I am asking for some help if possible.

Here is an example:

A work order was created on 02/23/2001 7:30 AM (Friday)
The mechanic got the work order at 02/26/2001 09:15 AM (Monday)
The companies work hours are from 6:00 AM - 3:30 PM Monday through Friday
The answer can be in hours. (11.25 hours)

And if it wasn't bad enough they asked me to exclude holidays. if I could.
**** those Monday holidays.

[Ted McNeal]

Dwight,

I am sure that your initial thought is that this is impossible. It really
would be insane if you had to implement such functionality just using date
functions.

You can do what you want to do by using a table to record the workdays and
the hours in that workday. This table would be similar to a date dimension
table in a Star schema database.

The table should include a record for each day of each year for a time period
several years off. The table should include the calendar date, a bit field
for whether the date was a work day, the starting time of work and the ending
time of work. You will then be able to count the number of work days between
your order placed date and completed date and compute the number of opportunity
work hours between.

You can generate the entries using DAO or ADO by looping from a start date
to a selected ending date and inserting a record for each date. To further
expedite your work, you can check if the date is a known day off and flag
it appropriately.

This should be enough of an idea to get you started and see how you might
begin to attack this task.

Ted McNeal
tmcneal@mhsinc.org



"Dwight" <hbliss@frontiernet.net> wrote:
>
>I am working with an Access database with dates and times. I have been
using
>the DATEDIFF Function in a SQL statement to get the difference between 2
>dates/times. This has been working fine.
>
>Recently it was asked of me to look at the difference between the 2 dates
>and excluding the times that the business is not in operation. I am not
>having much luck and I am asking for some help if possible.
>
>Here is an example:
>
>A work order was created on 02/23/2001 7:30 AM (Friday)
>The mechanic got the work order at 02/26/2001 09:15 AM (Monday)
>The companies work hours are from 6:00 AM - 3:30 PM Monday through Friday
>The answer can be in hours. (11.25 hours)
>
>And if it wasn't bad enough they asked me to exclude holidays. if I could.
> **** those Monday holidays.
>