MS Access Expression/Code - Up for a Challenge?

[proflola]

Hi all,

I'd like to see if I can get this working in Access...

Here's the scenario--I have a client who runs a Help Desk. I've developed an Access database that links to the Help Desk data so that I can do more extensive reports for the client.

I need a formula or coding that will do the following:

The Help Desk is open from 7:00 - 5:00 PM for a total of 10 hours. They are not open on the weekends. So.....here's what I need.


The fields that I'll be doing the calculation on are:

RecvdDateTime
ClosedDateTime

I need to subtract the RcvdDateTime from the CLosedDatetime, HOWEVER

The hours during the 10 hour workday are figured as regular hours/minutes. If a call extends over a workday only the hours worked will be calculated (meaning that the non-work hours will be excluded as well as the weekends). I need the results to reflect hours and minutes. It would also be nice to be able to subtract the holidays.

This is beyond my capabilities, however I've used Visual Basic coding in the past, so I'm not totally unfamiliar with it.

Currently, I do my queries in Access and then export it to Excel and use the NETWORKDAYS function statement to perform the necessary calculations. It would be so nice to be able to stay in Access. I would like to automate the process so that my client will be able to run the reports easily.

Here's a copy of the networkdays function statement.

It looks to variables:

Open (7:00 AM)
Closed (5:00 PM)
Total HOurs (10)
Holidays (listed)

IF(NETWORKDAYS(B8,C8,$E$2:$E$4)-1>0,(C8-(INT(C8)+$C$2))-(B8-(INT(B8)+$C$3))+(NETWORKDAYS(B8,C8,$E$2:$E$4)-2)*$C$4,C8-B8)

Thank you!