Time Average

[Lynn]

I am building a database for a call center. One of the metrics they track is how long each CSR spends on the phone for each call (ex. 5 min. 26 sec.). I have set this field up in the table as a short time format (5:26). The next step is to get an average of time spent on calls for all CSRs (ex. CSR1 spends 5:26 and CSR2 spends 4:50, average is 5:08). However, when I try to do the average in a query, I don't get a time format at all; I get a decimal number. I've tried everything I can think of. Any suggestions

Thanks!

 

[Rick B]

A time format is used for the time of day, not duration.

Access does not have a format for duration which uses minutes and seconds.

You should be using a decimal. Such as 1.5 for one and one half minutes.
This is the only way you will be able to perform math on the items.

If you wish to do some fancy math in a text box to convert this to the
format desired, you can do so.

Rick B



I am building a database for a call center. One of the metrics they track is
how long each CSR spends on the phone for each call (ex. 5 min. 26 sec.). I
have set this field up in the table as a short time format (5:26). The next
step is to get an average of time spent on calls for all CSRs (ex. CSR1
spends 5:26 and CSR2 spends 4:50, average is 5:08). However, when I try to
do the average in a query, I don't get a time format at all; I get a decimal
number. I've tried everything I can think of. Any suggestions?

Thanks!

 

[Lynn]

Thank you for your help. What sort of forumla could I use to convert it back to the time format?

 

[Rick B]

There should be some samples out there somewhere. Off the top of my head,
I'd probably...

1) round it to a whole number
2) subtract the rounded from the original
3) multiply by 60


for example 1.25
1) 1.00
2) 1.25 - 1.00 = 0.25
3) 0.25 * 60 = 15

Then I'd build my number such as...

[field1] & ":" & [Field3]
1:15


There may be more efficient ways, but something like that would work. I bet
you could even make a public function out of it so you could use hte math
over and over on difffernt numbers.

Rick B

Thank you for your help. What sort of forumla could I use to convert it back
to the time format?