Convert Text to DD:HH:MM

[cpufixer1]

I have a report of incident tickets. The Open time for each ticket is in the form of a text field DD:HH:MM. I need help getting the aveage open time at the footer. So I need to covert DD:HH:MM to a number and then some how aveage that and then display that back in DD:HH:MM at he bottom of the report.

Thansk for your help in advance.

 

[vega83]

Do a dateadd or date part with your field and currentdatetime in minutes And then again convert it back to datetimeformat

 

[vega83]

Create formula time
DateDiff ("s", datetime1, datetime2)

then create other formula:
dateadd('s',
Use below formula
whileprintingrecords;
local numbervar avtime:= average(@time,groupfield);

local numbervar hour:= int(avtime/3600);

local numbervar min:= int((remainder(avtime,3600))/60);

local numbervar sec:= remainder(remainder(avtime,3600),60);

totext(hour,0)&":"totext(min,0)&":"totext(sec,0)

Where @Time is your date diff formula.

Vega

 

[cpufixer1]

Thanks Vega, but I am still a bit confused. I have a report with a 150 records on it. The field I need to aveage is the is the close time of each record. This time field is in text. Also it is in DD:HH:MM not, HH:MM:SS. I need the aveage of these close times at the footer.

Thanks again.

 

[vega83]

use datetime(yourfield), above formula will work

 

[cpufixer1]

Thanks again Vega. My field DD:HH:MM is not really a date. it is the lenght of time. So if it's 1:04:05, the incident ticket was open for 1 day, 4 hours and 5 minutes. I have hundrends of these times on a report and I need an aveage.

 

[lbass]

You should convert it back to minutes by using a formula like this:

val(left({table.string},2))*1440+
val(mid({table.string},4,2))*60+
val(right({table.string},2))

Then you can insert an average on this formula.

Then adapt the method in faq767-3543 to convert the average back to a string. You could set "dur" = average({@duration})*60 so that it is in seconds.

-LB

 

[cpufixer1]

Thanks lbass, that is somewhat working. The problem is the DD:HH:MM can sometimes change in length. I need this formula to look for the ':' colons in between instead of the position of the digits.

 

[lbass]

Then use something like this:

stringvar array x := split({table.stringdate},":");
val(x[1])*1440+
val(x[2])*60+
val(x[3])

-LB

 

[cpufixer1]

That worked ! how do I get it back to DD:HH:MM after I aveage it at the footer ?

 

[lbass]

Please see my previous post.

-LB