×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Time Divide

Time Divide

Time Divide

(OP)
Hello,

I have created a report and in the report I wanted to calculate "total notes code time" by "total handle time" to get the % of notes code. The time has already been converted to h:nn:ss in the query. Below is the sample data, please help.

Total Notes Time 4:56:33
Total Handle Time 123:53:44
%Notes 3.99%
Thanks in advance!

I forgot to add this is in Access 2010.

RE: Time Divide

Hi,

How is your application getting the data from Access?

Are you executing an Access query from VB? Your time fields ought to be Date/Time, which are really NUMBERS. Like 6:00 is really 0.25, which is 6/24 of a day.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Time Divide

(OP)
The data is imported from Excel and then an expression is used to get the total handle time. To sum the totals I had to change the format to h:nn:ss. That all works fine until I try to divide the totals in the report so that I can get the % of notes taken.

RE: Time Divide

“To sum the totals I had to change the format to h:nn:ss”

Please post your code. I’m guessing that you used the Format() function that converts your time duration value to string.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Time Divide

(OP)
I used the following all the time fields.

TreatmentTime: (24*Int(Sum([AgentEffectivenessRaw].[ACD_Treatment_Time]+[OACD_Treatment_Time]+[ACD_Email_Interaction_TIme]))+Format(Sum([AgentEffectivenessRaw].[ACD_Treatment_Time]+[OACD_Treatment_Time]+[ACD_Email_Interaction_TIme]),'h')) & Format(Sum([AgentEffectivenessRaw].[ACD_Treatment_Time]+[OACD_Treatment_Time]+[ACD_Email_Interaction_TIme]),':nn:ss')

Thank you!

RE: Time Divide

Format() returns a STRING and a string does not go very well with math.

The question is are all your fields Date/Time fields or are some OTHER numeric types?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Time Divide

(OP)
All the time fields are Date/Time, only calls answered is a number field.


Thank you

RE: Time Divide

Quote:

calls answered is a number field.

What units of time do those fields represent?

Date/Time fields have units of Days.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Time Divide

(OP)
I am not sure if I understand the question but the fields are for hours: minutes and Seconds, I do not want it to roll that into days.


Example:
Total Notes Time 4:56:33
Total Handle Time 123:53:44

RE: Time Divide

It looks to me you need to re-calculate:
4:56:33 to 4.9425
123:53:44 to 123.895555555556
then divide one by the other and * 100 to get the %Notes, right?

Something like this?

CODE

Dim t1 As String
Dim t2 As String
Dim Temp As String
Dim dbl1 As Double
Dim dbl2 As Double

t1 = "4:56:33"
t2 = "123:53:44"

Temp = "0:" & Split(t1, ":")(1) & ":" & Split(t1, ":")(2)

Debug.Print Split(t1, ":")(0) + TimeValue(Temp) * 24
dbl1 = Val(Split(t1, ":")(0) + TimeValue(Temp) * 24)

Temp = "0:" & Split(t2, ":")(1) & ":" & Split(t2, ":")(2)

Debug.Print Split(t2, ":")(0) + TimeValue(Temp) * 24
dbl2 = Val(Split(t2, ":")(0) + TimeValue(Temp) * 24)

Debug.Print "%Notes " & (dbl1 / dbl2) * 100 

If so, this code should be wrapped into a small Function accepting 2 strings and returning a double.


---- Andy

There is a great need for a sarcasm font.

RE: Time Divide

If they are numeric fields and not Date/Time, the each field must be of ONE UNIT: either hours, or minutes, or seconds, since Date/Time is numeric units of Days.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Time Divide

You need to choose which units of time to normalize all of your data values. You can convert to Days or Hours or Minutes or Seconds. Your choice.

But given the results of that choice, then each value that is not of that selected unit of time, needs to be converted to that selected unit of time.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Time Divide

So, embedded in your TreatmentTime formatting would appear to eb the solution to your problem: Sum([AgentEffectivenessRaw].[ACD_Treatment_Time]+[OACD_Treatment_Time]+[ACD_Email_Interaction_TIme]) is the numeric representation of the duration. SO you should be able to directly divide the unformatted number representing Total Notes Time by this value, and bingo.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close