MS Access: how to calculate business hours between two dates

[iceone]

Hi everybody,

I am monitoring a call center and i need to calculate the working hours between the moment the call is answered and the moment the call is closed.
i need to calculate the working hours difference (ie excluding nights, sundays, saturdays, thanksgiving, ecc,)

i consider as working hours from 09;00 am to 19:30 pm (monday-fryday)
(i.e. from fryday 19:00 to monday 09:30 i want the function to accont for one hour)

have u already built a module for this purpose?

thank u all

[puppydogbuddy]

wow, you've asked for the moon! Can't give you all that you asked for, but maybe this will help you get started:

This link is to a free (without source code) complete call logging database application. If you want the source code, the vendor charges $40.

http://www.aadconsulting.com/dbdld.html

I have never used the above db application, so I don't know how it computes logging time.

To give you an idea of how it can be done, here is an example: If your logging time were in minutes and seconds, a simple solution would be to find the time difference between two times using the standard DATEDIFF function.

In the example below, time variables ctime1 and ctime2 represent the first and second date/times…….using the date/time field provided by MS Access And the time is computed using the Parameter "N" (seconds) in the DateDiff function. If your logging intervals need to be computed in hours, you can change the DateDiff function parameter to compute hours, etc.

Since the above does not factor weekends, holidays, you would have write/obtain publicly available functions posted on various web sites by googleing.

Dim cTime1 
Dim cTime2

timediff = ABS(DateDiff("N", ctime1,ctime2))

No specific time format has been applied.

Hope this helps.

[nico5038]

You might also check the Date/Time functions at:

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

Nic;o)

[youmike]

Basically, there is really no reliable alternative to purpose built code which takes start date & time, end date & time and checks each successive date for day of the week and then in a table which lists the holidays for your particular location. It's not an uncommon need, but because different countries have different holidays, some of which (Easter, for instance - different in Greece from Italy) change actual date from year to year, I doubt that an of the shelf global solution exists.

Sorry to be so negative.