PLEASE HELP...Sum of String field formula needed... 2

[EagerBeaver]

I cannot get this formula to work. I need to have summary totals for a string field which looks like this: 00:00:00:00 It contains days:hours:minutes:seconds: I need to add the total time for each group and also be able to graph it. Please help, I am pretty sure I will get the graph if I am able to get the summary totals and the grand totals.

Thanks in advance.

 

[MalcolmW]

The summary operations available for string fields are things like max, min, and count. You have to convert it to a numeric value, which in this case will need to be expressed in seconds.
This is fairly straightforward if the string is a constant length i.e. "01:12:05:36", rather than "1:12:5:36". You can convert each portion of the string to a numeric value, and multiply it by an appropriate factor to get seconds, and then add all the pieces up to get the total seconds equivalent for your string field.
You can also do something like
StringVar TimeString := {YourField} ;
DateDiff ("s", Date(999,12,31), DateTime (DateSerial(999,12,31+(Val((Timestring)[1 to 2]))), Time ((Timestring)[4 to 11])))
Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[EagerBeaver]

thanks for the input malcolm...

I have tried to convert it to numeric and it still does not work for me... Could you please tell me where I could find that formula?

 

[MalcolmW]

(Val({YourField})[1 to 2] * 24 * 60 * 60) +
(Val({YourField})[4 to 5] * 60 * 60) +
(Val({YourField})[7 to 8] * 60) +
(Val({YourField})[10 to 11]
will give you the give you a numeric equivalent, expressed in seconds, for the string field.
Is that the part you are having a problem with? If so, what sort of error are you getting? Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[EagerBeaver]

I tried that... the error I get is " A string or array of values is required here"

the field is a string. I am sure of that

I JUST CANT GET THESE TO TOTAL.

 

[kenhamady]

Copy and post your exact formula here, the one that errors out, so we can check the syntax. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[EagerBeaver]

here is what the field looks like 000:00:00:00

here is the formula:

(Val({ISSUE_ACTIONS.IA_TIME_SPENT})[1 to 3] * 24 * 60 * 60) +
(Val({ISSUE_ACTIONS.IA_TIME_SPENT})[5 to 6] * 60 * 60) +
(Val({ISSUE_ACTIONS.IA_TIME_SPENT})[8 to 9] * 60) +
(Val({ISSUE_ACTIONS.IA_TIME_SPENT})[11 to 12] )

 

[kenhamady]

Your paren was in the wrong place in each line, it should include the square bracket. Try this:

(Val({ISSUE_ACTIONS.IA_TIME_SPENT}[1 to 3]) * 24 * 60 *60) +
(Val({ISSUE_ACTIONS.IA_TIME_SPENT}[5 to 6]) * 60 * 60)+
(Val({ISSUE_ACTIONS.IA_TIME_SPENT}[8 to 9]) * 60) +
(Val({ISSUE_ACTIONS.IA_TIME_SPENT}[11 to 12]) ) Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[MalcolmW]

Oops - my fault - thanks for the fix Ken Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[kenhamady]

Malcolm,

Did you see my post the day before yours?
thread 149-83007

Great minds think alike.
Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[EagerBeaver]

Thanks for all the help so far fellas...

I entered the formula and also got inserted a summary total of the field.

However, I am getting the data in seconds. I need it to display in the same format i.e. 000:00:00:00

this is the formula I am using for converting it back. It is not working. It tells me that a &quot" is missing.

Please help again.


Totext ( Days, '00', 0,'') + ':'+
Totext ( Hours, '00', 0,'') + ':'+
Totext ( Minutes,'00', 0,'') + ':'+
Totext ( Seconds,'00', 0,'')

 

[kenhamady]

I have this formula in my FAQ on common formulas in the General CR forum. Use the entire formula and read the instructions for putting in your field. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.