I have a telephone call log style database which contains the following fields:

Log Date

Log Time

Close Date

Close Time

I already use a number of calculation fields to give me how many hrs/mins/secs a call was open for (calculated upon entry of the Close Date & Time). This works fine, but my next step is to calculate the number of 'Working Hours/Mins & Secs' (Between the Log Date/Time and Close Date/Time) a call has been open for based upon a 9 - 5 working day.

Some calls stay open for a few minutes, others can be several days.

Where do I start with this one!! ?

Hi,

Create a calculation field (lets call it cDurationOfCall)

(Close Date - Log Date)*86400 + (Close Time - Log Time)

Now, I know that this is just the beginning, because I built a call control solution that had to take into account 9-5 (but also weekends and bank holidays). I will see if I still have that solution.

Hi Matt,

While waiting for Andy to provide his solution, another thing you may consider is displaying the status of the field as well. If you make the calc num, unstored and apply:

Case(IsEmpty(CloseDate) or IsEmpty(CloseTime), "Open", (CloseDate - LogDate)*86400 + (CloseTime - LogTime))

This will flag any calls still open and eliminate the funky problems caused by logs that are still open (ie, Close date/time fields are blank). Been there ... done that!!

LaRetta

Thanks for the replies Andy / LaRetta,

Would appreciate that example Andy.

Cheers

Matt

Matt,

The example I have (which I have to strip from the solution) is based around a 9-5 date.

as I said, this was for a call control centre, that had response times required etc. It allowed for 9-5, weekends and bank holidays.

It also had percentages for calls that met the allotted times, and those that did not. Average response times, etc, etc, etc.

It may take a while to strip out (as I am getting ready for a major launch myself), but if you can wait I will get it to you as soon as I can.

I posted a sample file somewhere in this forum a few months ago that does date calculations allowing for weekends and holidays. I've revised it a bit and reposted in the samples area:

http://www.fmforums.com/threads/showflat.php?Cat=&Board=files&Number=54679&page=0&view=collapsed&sb=5&o=31&fpart=1

Maybe this will give some ideas.