I am writing an agent that grabs data from a text file and converts certain columns to fields in a new document. Several of the fields deal with Average duration of calls, or hold times.
I want to be able to combine several docs into one that gives totals and averages. The data is in mm:ss format. Is there a script way to average these? Currently I’m separating them as string values with strleft (,“:”) and strright…then adding, averaging and re-combining…surely there is a better way?
Subject: you may be able to try timevalue?
I think what you are doing is common. Alternatively, you can try to use the TimeValue function that can do some text conversion for you, but you have to put in an arbitrary hour, then subtract it from that hour. It gets a little confusing, but the easiest thing to keep it straight is the time at absolute “0” is based a hard code date in time–12/30/1899 12:00am. So, as you add to your variant variables, keep that in mind that when you convert it do a double, it is how many days from that point in time. (Note, days are whole integers, hours are divided by 24 hrs in a day, minutes are divided by (24hrs * 60 min in a hour), and seconds are divided by (24 hrs * 60 min * 60 seconds in an min)–sexagesimal.
dim var1 as variant
var1 = timevalue(“1:” & )
var1 = var1 - timevalue(“1:00:00”)
Then, if you do a cdbl(var1) should measure your minutes/hour in a fractional form for later numeric calculations. For instance, I think 1:30 will be 0.0010416666 days which is correct because that is mathmatically it represents: 1/(24h * 60m) + 30/(24h * 60m * 60s).
If you just do a var1 on what you expect to be 1:30, you will get it relative to 12:00 am, so it would be 12:01:30am.
Later on, you can take the whole number that represents the days, and assign the fractional part to variable (maybe with help of the Mod or Div functions). multiply the fraction by 24, and the whole part is the hours. Multiply the factional part by another 60, the whole number is the minutes, etc.
-Kyle Huang