Converting DateTime output from number to real hh:mm:ss

[cathey04]

I am using crystal 10, HP Service Desk (Oracle 9/HPUX), and an Oracle ODBC driver. I found the following formula in FAQ and another post and it worked great, but the output is text and I need it to be the actual hh:mm:ss, so that I can average the time in Crystal or Excel. We are trying to track the average time a service call is created to when the call was accepted by another team to fix the problem. What would I need to change in order to do this? I have spent hours trying to figure this one out. Any help would be fabulous!

numberVar dur := datediff("s",{V_SERVICECALL.CREATED}, {V_SERVICECALL.ACTUALSTART}); //get the seconds between 2 dates
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");

hhmmss

 

[synapsevampire]

Try:

whileprintingrecords;
numberVar dur := datediff("s",{V_SERVICECALL.CREATED}, {V_SERVICECALL.ACTUALSTART}); //get the seconds between 2 dates
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);
ctime(hrs,min,sec)

-k

 

[cathey04]

Is the -k at the end significant? I took it out because I kept getting "A number, currency amount, boolean, date, time, date-time, or string is expected here"

Then if I take the -k out, there are no formula errors, but when I go to the Cross-Tab I created, that particular formula doesn't show up.

 

[synapsevampire]

You didn't mention a cross-tab before...

Try it without the whileprintingrecords;

-k

 

[lbass]

I think you will have to do the calculations using seconds. You can then format the result in hh:mm:ss format by using the display string formula area on the common tab (format field->common->display string).

-LB

 

[cathey04]

Thanks both of you. LB - what would the formula look like to this. Sorry about all the questions, my two Crystal Books don't have anything about datetime.

 

[synapsevampire]

Post example data and expected output.

-k

 

[lbass]

You should post what your row and column fields are, and what you want for the summary. You probably would use a datediff formula like the following and then insert an average on it:

datediff("s",{V_SERVICECALL.CREATED}, {V_SERVICECALL.ACTUALSTART})

Then you would go to the display string formula area and do the conversion using SV's FAQ:

numberVar dur := currentfieldvalue;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");

hhmmss

If it could take days then use something like this:

numberVar dur := currentfieldvalue;
numberVar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar ddhhmmss;
days := Truncate(dur/86400);
hrs := truncate(remainder(dur,86400)/3600);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);
ddhhmmss := totext(days,"00")+ ":"+totext(hrs, "00") + ":" + totext(min, "00") + ":" + totext(sec, "00");
ddhhmmss;

-LB

 

[cathey04]

The first column is a workgroup and the numbers at the end are supposed to be the average time in hh:mm:ss that it took each group to accept their calls. So the helpdesk creates it, can't resolve it, so they assign it to another group. We need to know the average time (from a week's worth of calls) it took said group to accept the calls.

Total
Tier 3 AppSupport ADSI 4
Tier 3 AppSupport AHRIS Payroll 1
Tier 3 AppSupport ARMIS 1
Tier 3 AppSupport Associate (B2A) Portal 5
Tier 3 AppSupport CGT Trainin 1
Tier 3 AppSupport Dallas System 2
Tier 3 AppSupport Decision Cast 7
Tier 3 AppSupport Distribution & Procurement 10
Tier 3 AppSupport EAM 1
Tier 3 AppSupport Ecommerce 17
Tier 3 AppSupport EDW 8
Tier 3 AppSupport EDW Data Requests 12
Tier 3 AppSupport EM 63
Tier 3 AppSupport GIFTS 2
Tier 3 AppSupport HCS In Store Pharmacy 1
Tier 3 AppSupport HCS Mainframe 2
Tier 3 AppSupport Integration Applications 3
Tier 3 AppSupport ISET 1
Tier 3 AppSupport Jewel 7
Tier 3 AppSupport Kintana 4
Tier 3 AppSupport Legacy Financials - BOI 6
Tier 3 AppSupport LMS 10
Tier 3 AppSupport Location Master 1
Tier 3 AppSupport MDEP 2
Tier 3 AppSupport Merchandising 1
Tier 3 AppSupport Oracle Financials 36
Tier 3 AppSupport PeopleSoft Payroll 37

 

[cathey04]

Don't both of those formulas output it to a string type, instead of a real time format? When I just use the following formula to figure how how long a call is open, I get hours. Would I need something similar?

(
//If ticket is still at an unresolved status use current date and time
IF ISNULL({V_SERVICECALL.ACTUALFINISH})
THEN CurrentDateTime
ELSE {V_SERVICECALL.ACTUALFINISH}
)
-
//If no start date was set use the open time of the ticket
(
IF ISNULL({V_SERVICECALL.ACTUALSTART})
THEN {V_SERVICECALL.CREATED}
ELSE {V_SERVICECALL.ACTUALSTART}
)
)
//Used to convert days to hours
*24

 

[lbass]

I don't know what your question is. As I mentioned earlier, you can't average a time field directly. If you are doing the calculation in seconds (or hours), why does it matter whether the display is a string or a real time? Anyway, in the display string formula area, the result must be a string, so you would need one of the formulas I suggested.

-LB