Trying to convert a number to Time 4

[kcsilentbob]

I use AWD which pulls time in to Crystal as HH:MM:SS. So data displayed on crystal is listed as 5,107.00 which should be 00:51:07.

Here is my problem:

I need that number to be read as time. What is happening is that I am trying to average a number as time and of course it is not reading properly.

Please help

 

[DataDog]

First of all, Welcome to the Group! (Clicking on your handle tells us the first time you registered).

I think you will find this to be an excellent source of information IF you will help us help you.
Generally when asking a question, it would be helpful if you tell us:
1. What Version of Crystal you are using.
2. What Database you're connected to
3. Define any Acronyms (AWD).
4. What datatype that you are bringing into Crystal. (This one looks like a number, but it might be a string).
Thanks in advance.

DataDog
'Failure Is Not An Option'

 

[kcsilentbob]

Thanks we have had virtually no training on crystal, and I am the so called expert because I am unafraid to try new things.

1. Version 7.0
2. We are working on a citrix main frame(?)
3. Automated Work Distributor (DST product)
4. Not sure what the data type is but the value is pulling 5,107 and is actually 0:51:07.

I hope this is what you need but like I said I am a super novice when it comes to formulas or functions in crystal.

 

[bryan3473]

Create the following formula:

//Replace 1.38 with your number or number field

NumberVar nbrTime := 1.38;
NumberVar nbrHour := Truncate(nbrTime);
NumberVar nbrHold := (nbrTime-nbrHour)*100;
nbrHold := (60*nbrHold)/100;
NumberVar nbrMinute := Truncate(nbrHold);
nbrHold:= (nbrHold-nbrMinute)*100;
NumberVar nbrSecond := Truncate(nbrHold*60)/100;

ToText(nbrHour,0,"") + ":" + ToText(nbrMinute,0,"") + ":" + ToText(nbrSecond,0,"")


This formula will display a number field in the 'hh:mm:ss' time format

hope that works if not let me know

Bryan
Bmcafee@regio-tile.com

 

[bryan3473]

hey im sorry that was for version 9.0 here is a link to a pdf since your on 7.0

http://support.businessobjects.com/communityCS/TechnicalPapers/scr567_datetimefunctions.pdf

Sorry about that

 

[DataDog]

Brian: Please note that in his original request, he was going to have to AVERAGE some totals. Your "ToText" solution won't quite work for that.

kcsilentbob : One more request: Please give more examples of the input data (more than your 5,107.00 example) and the expected outputs. I.E. if the input number was 10,001.01, what do you expect the output to be? Feel free to tell more about the "Averages" that you are ultimately trying to accomplish.

DataDog
'Failure Is Not An Option'

 

[kcsilentbob]

This is the current data set
Time
1035 12.00
1035 4.00
1035 1,346.00
1035 30.00
Avg. 348

I would like for it to read
1035 00:00:12
1035 00:00:04
1035 00:13:46
1035 00:00:30
Avg. 00:03:48

The decimal point on the current data can be deleted by formatting, and is not required.

 

[lbass]

You should first convert the number to seconds so that you can insert an average on it. Try this formula which allows for hours as well as minutes and seconds {@seconds}:

val(right(totext({table.number},0,""),2))+
val(strreverse(mid(strreverse(totext({table.number},0,"")),3,2)))*60 +
val(strreverse(mid(strreverse(totext({table.number},0,"")),5,2)))*3600

Place this in the detail section and right click on it->insert summary->average. You can then convert the average in a formula like the one in SynapseVampires FAQ767-3543. Just substitute the following:

numberVar dur := average({@seconds},{table.groupfield});

This assumes you are evaluating the average at a group level.

-LB

 

[kcsilentbob]

I inserted your formula and got this: The ) is missing

What causes that?

 

[lbass]

A non-matching number of open and close parens. Try copying my formula above into your formula expert and then just replace the {table.number} with your actual field. I believe the formula is correct as is.

-LB

 

[kcsilentbob]

I still got the error. Here is what I did:

val(right(totext({W82U999S.ACTTIM},0,""),2))+
val(strreverse(mid(strreverse(totext({W82U999S.ACTTIM},0,"")),3,2)))*60 +
val(strreverse(mid(strreverse(totext({W82U999S.ACTTIM},0,"")),5,2)))*3600

Where W82U999S is my table and ACTTIM is my field

 

[kcsilentbob]

Also I don't see strreverse on crystal v. 7

 

[synapsevampire]

Don't have time to post the code, but create a formulka which converts the nummber to a datetime, then use a datediff function to convert it to seconds.

Coding out the formula is eomthing like:

stringvar MyTime:=totext(57,0,"");
timevar MyTime;
numbervar secs:= 0;
if len(MyTIme) = 1 then
MyTime:= time("00:00:0" & mid(MyTime,len(MyTime),1))
else
... you get the idea

There are other ways, but this is generic enough for ver 7.

The last part of the formula would convert the time to a datetime using todays date, and then do a datediff against todays date at midnight to get the seconds for use with averaging.

Should get you close.

-k

-k

 

[kcsilentbob]

Sorry synapse I am not sure that I follow this. Where would the table go?

 

[DataDog]

kcsilentbob: I'm going to go way on on a limb here. It looks to me that your raw data might be nothing more than Seconds. A few posts up, you said:.....

1035 12.00
1035 4.00
1035 1,346.00
1035 30.00
Avg. 348
And I agree that your average (348) is correct so far....

You continued.....
I would like for it to read
1035 00:00:12
1035 00:00:04
1035 00:13:46
1035 00:00:30
Avg. 00:03:48

According to my math, that total is 14:32
Dividing by 4 gets to 3:38, NOT 3:48.

It's that 1,346.00 entry that I'm questioning. If that really means 13 minutes 36 seconds, I'd be quite surprised.

Could you go back to the DBA that designed the database(or someone that is knowledgable) and verify that please?

P.S. With lbass and synapsevampire on this, you've got the 2 best Ninjas around (note #1 and #2 on the MPV List to the right)
P.S.S. Any way to get rid of that Rev 7?

DataDog
'Failure Is Not An Option'

 

[lbass]

Sorry, I didn't notice the v.7. If the len() function is available to you, then you could try this:

val(right(totext({{table.number}},0,""),2))+
(
if len(totext({{table.number}},0,"")) = 3 then
val(mid(totext({{table.number}},0,""),len(totext({{table.number}},0,""))-2,1))*60 else
if len(totext({{table.number}},0,"")) >= 4 then
val(mid(totext({{table.number}},0,""),len(totext({{table.number}},0,""))-3,2))*60
) +
(
if len(totext({{table.number}},0,"")) = 5 then
val(mid(totext({{table.number}},0,""),len(totext({{table.number}},0,""))-4,1))*3600 else
if len(totext({{table.number}},0,"")) = 6 then
val(mid(totext({{table.number}},0,""),len(totext({{table.number}},0,""))-5,2))*3600
)

If you don't have val() available, you could use tonumber().

-LB

 

[kcsilentbob]

lbass that formula hit it! I had to change the LEN to LENGTH but i am now hit the seconds perfectly. I and my company thank you!

Also We are able to make to changes on the DB level. My admin. guy changed to field to a set six figure field (exp. 000348, 001323) but he named the field HHMMSS. I tried to write the code for this (using your previous without the strreverse) and it errored out. Could the cause for this be the name of the field itself? The main error was Too many arguments on the to text portion of the formula.

Again THANKS a bunch, and I really appreciate your help!

 

[DataDog]

The name probably doesn't make any difference, but if he's dinkin with the DB, you may want to go up to the top and hit Datatabase, then select "Verify Database" to make sure you're in sync. Also, you may want to verify with your admin. guy that he didn't change data types on you. (i.e. from a number to varchar (a character string) or something like that.

DataDog
'Failure Is Not An Option'

 

[kcsilentbob]

Ok I have the average working on the formula provided by lbass. This does a great job of putting eveyrthing into seconds.

Know can you help me move this over to a traditional hh:mm:ss format?

Again you guys are awesome thanks for your help!

 

[DataDog]

Since you now have exactly 6 characters in the DB field, I would try a new formula "@FormattedTime" and set it to:

Time (tonumber(left(totext({W82U999S.ACTTIM},2),(tonumber(mid(totext({W82U999S.ACTTIM},3,2), tonumber(right(totext({W82U999S.ACTTIM},2))

which should give you Time (HH , MM, SS) then right click and format the field to HH:MM:SS

DataDog
'Failure Is Not An Option'