Number to Time 1

[ptrifile]

I currently have the following formula to get an Average Speed of Answer:

if ({splits_daily_history1.ans_before_ann}+{splits_daily_history1.ans_after_ann1}+{splits_daily_history1.ans_after_ann2})=0 then 0 else {splits_daily_history1.total_duration_count}/({splits_daily_history1.ans_before_ann}+{splits_daily_history1.ans_after_ann1}+{splits_daily_history1.ans_after_ann2})

on the report it is formatted as a NUMBER. I need it to be formatted as HH:MM:SS. I have tried using TOTEXT but to no avail, can someone help me modify my existing formula so its formatted as TIME?

Thank you for any help!

Paul

 

[CoSpringsGuy]

I use this formula created by someone else on here.. I think Lbass but I'm not sure....

WhilePrintingRecords;
numberVar dur := {@yourformula} ;
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,"00") + "h:" + totext(min,"00") + "m"+totext(sec,"00")+"s";
hhmmss

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

This is from faq767-3543.

-LB

 

[ptrifile]

Thanks guys, that gave me the exact desired results. However, now my Group Footer will not average that field, and when i try and when i try and insert a summary on the group footer that formula field is not available as an option. Is there a workaround?

Thanks again for all the help!

Paul

 

[CoSpringsGuy]

create another formula

//
WhilePrintingRecords;
numberVar dur := sum({@yourformula},{groupfield}) ;
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,"00") + "h:" + totext(min,"00") + "m"+totext(sec,"00")+"s";
hhmmss

put that in your footer?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

//
WhilePrintingRecords;
numberVar dur := average({@yourformula},{groupfield}) ;
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,"00") + "h:" + totext(min,"00") + "m"+totext(sec,"00")+"s";
hhmmss


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[ptrifile]

Thanks CoSpringsGuy, im getting an error when trying to save the forumula, i have the following entered:

whilePrintingRecords;
numberVar dur := average(if ({splits_daily_history1.ans_before_ann}+{splits_daily_history1.ans_after_ann1}+{splits_daily_history1.ans_after_ann2})=0 then 0 else {splits_daily_history1.total_duration_count}/({splits_daily_history1.ans_before_ann}+{splits_daily_history1.ans_after_ann1}+{splits_daily_history1.ans_after_ann2}),{splits1.name}) ;
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,"00") + "h:" + totext(min,"00") + "m"+totext(sec,"00")+"s";
hhmmss

And i get an error stating "a field is required here" and it highlites my code from IF to the comma before the group name.

I'm probably doing something wrong but i dont know what it is....thank you once again.

 

[CoSpringsGuy]

dont put the formula itself use the identifier for the formula

so replace:
average(if ({splits_daily_history1.ans_before_ann}+{splits_daily_history1.ans_after_ann1}+{splits_daily_history1.ans_after_ann2})=0 then 0 else {splits_daily_history1.total_duration_count}/({splits_daily_history1.ans_before_ann}+{splits_daily_history1.ans_after_ann1}+{splits_daily_history1.ans_after_ann2}),{splits1.name})

with

average({@formulaname},{splits1.name})

I assume {splits1.name} is the field you grouped on

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[ptrifile]

I now get the error "A number field or currency amount field is required here" im assuming because he formula already converted the field?

 

[CoSpringsGuy]

I dont understand why you would get that error but I am pressed for time today... try the following and if that doesnt work I will check back in later

-place your formula in your report details..
-right click and insert a average summary to the group footer
-delete the field from details (unless you need it there)
-open the second formula we created
-in Formula Workshop, look through your report fields and -find the one that we just created.
-Add that to the formula after numberVar dur (dont delete the
-Place that formula in your footer
-Delete the average summary from the group footer if you dont need it


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

-in Formula Workshop, look through your report fields and -find the one that we just created.

to clarify those steps.. find the group summary average we just created



_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[ptrifile]

CoSpringGuy, thank you for all of your help! I figured it out, i wasnt entering the data in correctly. All is working. Thanks again for your patience!!!!

Paul