Time Divide

[NanaRod]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[NanaRod]

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.

 

[SkipVought]

“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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[NanaRod]

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!

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[NanaRod]

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


Thank you

 

[SkipVought]

calls answered is a number field.

What units of time do those fields represent?

Date/Time fields have units of Days.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[NanaRod]

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

 

[Andrzejek]

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?

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

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.