convert time to seconds? ? 1

[darwin101]

I have converted strings into time < CR 6.0 >
the start time
TIME ( ToNumber ( {Query.Inv Start Time} [ 1 to 2 ] ) ,
ToNumber ( {Query.Inv Start Time} [ 3 to 4 ] ) ,
ToNumber ( {Query.Inv Start Time} [ 5 to 6 ] ) )

// The end time (the 'zereos' are when no end date entered)
IF ( {Query.Loc Stop Time} = '000000' ) Then
TIME ( ToNumber ( {Query.SYSDATE} [ 12 to 13 ] ),
ToNumber ( {Query.SYSDATE} [ 15 to 16 ]),
ToNumber ( {Query.SYSDATE} [ 18 to 19 ]))
else
TIME (ToNumber ( {Query.Loc Stop Time} [ 1 to 2 ] ) ,
ToNumber ( {Query.Loc Stop Time} [ 3 to 4 ] ) ,
ToNumber ( {Query.Loc Stop Time} [ 5 to 6 ] ) )
formated in cr as &quot;hh:mm:ss&quot;

the use this formula to find the difference
TIME ( 0,0,0 ) + ( {@Time Stop} - {@Time Start} )

Now what i cant figure out is how to get a subtotal on the time field (time difference) CR will only show me &quot;Min, Max, Count... I think the &quot;difference&quot; needs to be converted to seconds, but i can't find anything in my books. I did find Ken's method to convert seconds into real time, If i make it that far.

Any help would be appreciated

 

[dcm]

darwin101: Try amending you formulae to give you the number of seconds e.g.
TIME ( ToNumber ( {Query.Inv Start Time} [ 1 to 2 ] ) ,
ToNumber ( {Query.Inv Start Time} [ 3 to 4 ] ) ,
ToNumber ( {Query.Inv Start Time} [ 5 to 6 ] ) )
becomes
( ToNumber ( {Query.Inv Start Time} [ 1 to 2 ] ) * 360 +
ToNumber ( {Query.Inv Start Time} [ 3 to 4 ] ) * 60 +
ToNumber ( {Query.Inv Start Time} [ 5 to 6 ] ) )

Then when you've got 2 numbers its easy to subtract them to find the difference and to subtotal the result David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Crystal Decisions Enterprise Partner

 

[darwin101]

Thanks David, That did the trick

 

[cindyknapp]

I am new to CR and I've been successful in using these formulas in a report to calculation time differences. However, the report bombs with a message &quot;The string is non-numeric&quot;.

I looked at the data and it appears that the field(s) are null.

What do I need to do to keep the report from bombing?

 

[darwin101]

Hi Cindy I am in the same situation, i posted the issure on the formula forum and 'Rhinok'filled me in on what was going wrong. I was able to get around it by writing a sql query instead of using CR.
here is Rhinok's message:

This normally occurs when you name specific character positions (in Left and Right formulas, for example)and your field isn't static. For example, you'll see 12/31/2001 12:12:12 which is 19 characters in length Vs. 1/31/2001 12:12:12 which is 18 characters in length. If your formula is ToNumber(Left(ToText({TableName.FieldName}),2) for the first date then the return is 12. If your formula hits the second date it attempts to convert '1/' to a number and you get the infamous &quot;The string in non-numeric&quot; error message.

Try converting your Date Time values into String within your Report Options Form (File|Report Options). You should then be able to parse your field correctly because the value will be converted into a static format (MM/DD/YYYY 00:00:00, for example).