Convert Number into time

[Thant]

Greetings,
I have what I hope to be a simple question.
I have a field in my report (many fields actually) that convert time into a numerical value. I think it is a measure of seconds. It shows as a whole number like 297. I need to convert this into a time format to make it easier for the managers to understand. What kind of formula would I use to convert it into a HH:mm:ss format
Thanks in advance

 

[lbass]

We would have to know definitely whether it is seconds and also what the base datetime is. Can you show samples of the numbers and also the corresponding times that they represent?

-LB

 

[Thant]

I was just granted access to the database(nortel symposium) and as i looked at the data i dont think the fields i was looking at matched. I will do more research and post again with more data
sorry about that

 

[Thant]

I have researched the database and it is storing the whole number as seconds

Any ideas would be helpfull
thanks

 

[lbass]

Please respond to all questions in my post.

-LB

 

[Thant]

greetings,
After some research I think I have found the method to convert this. It requires standard syntax rather than crystal
Here is what I have found:
The field listed as wait time contains a value in the database as 28503. When using the formula below it converts that number into 07:55:03
This is what my original post was looking for.
My only question now is in regards to this total (07:55:03) how would I then take this formula and create a summary that calculates the Average. When using the summary function within crystal I only have the option for Max/Min etc and I know there is a way to do this in the formula editor but again not something I have done much of before.

Thanks again for all of your help in past and future

Dim hr As Number 'to hold hrs part
Dim mn As Number 'to hold minutes
Dim sc As Number 'to hold seconds
Dim r1 As Number 'to hold reminders
Dim r2 As Number 'to hold reminders 
Dim str As String 'to hold string hrs
Dim str1 As String 'to hold string minutes
Dim str2 As String 'to hold string seconds
hr=int({dAgentBySkillsetStat.WaitTime} \ 3600) 'get hours
r1=Remainder ({dAgentBySkillsetStat.WaitTime}, 3600 ) 'rest of seconds after hours taken out
mn=int(r1 \ 60) 'get minutes
r2=Remainder (r1, 60 ) 'rest of the seconds
'Convert all values in to strings to format in to "00" and to remove decimals (you can also use string formats)
str=CStr (hr) 
str1=CStr (mn)
str2=CStr (r2)
'Get rid of the decimal values (you can also use string formats)
str = left(str, len(str)-3)
str1 = left(str1, len(str1)-3)
str2 = left(str2, len(str2)-3)
'format in to "00" (you can also use string formats)
if len(str)<=1 then
    str="0" & str
else
   str= str
end if

if len(str1)<=1 then
  str1="0" & str1
else
  str1= str1
end if

if len(str2)<=1 then
  str2="0" & str2
else
  str2= str2
end if

'Finally concatinate and add it to formula
formula= str & ":" & str1 & ":" & str2

 

[lbass]

That was the missing piece. All you need to do is use Crystal syntax in the formula like this:

time(dateadd("s",{yournumberfield},currentdate))

For an average, you have to calculate based on your original field:

time(dateadd("s",average({yournumberfield},{table.group}),currentdate))

Remove {table.group} if you are calculating at the report level.

-LB

 

[Thant]

LB
That seems to have converted it into a time format like 4:09pm. I was trying to convert it into time elapsed type field HH:mm:ss. Am I missing something in your earlier post?


Thanks

 

[lbass]

Once it is a time, you can format it however you want--with hours, minutes, seconds. Just right click on it->format time.

I'm not sure what distinction you are making re: time elapsed vs. time. I thought you were converting seconds into a time format.

-LB

 

[Thant]

I am converting seconds into a time format. You are correct about formatting the field, sorry about the mixup my mistake.
Thanks for all your help, now I just have to figure out how to do a summary/average on these fields and ill be set to go

Thanks

 

[lbass]

I did explain how to do the average, too. You must average the seconds and then convert.

-LB

 

[Thant]

LB,
Ive been working with the formula for average that you sent me. I understand how it works but when you say place in group field im getting an error

time(dateadd("s",average({dAgentPerformanceStat.NotReadyTime},
GroupName ({dAgentPerformanceStat.Timestamp}, "daily")),currentdate))

When I run this I get
"This field cannot be used as a group condition field"

Am i missing something?
Thanks again for your help. I realize ive been a bit dense about this

 

[lbass]

You should not be using the group name. Use:

time(dateadd("s",average({dAgentPerformanceStat.NotReadyTime},{dAgentPerformanceStat.Timestamp}),currentdate))

You also don't have to specify "daily" as that is the default.

-LB

 

[Thant]

Im getting closer I have

time(dateadd("s",average({dApplicationStat.CallsAnsweredDelay},
({dAgentPerformanceStat.Timestamp})),currentdate))

I get
" There must be a group name that matches this field"

I do have a group named dAgentPerformanceStat.Timestamp and im trying to average the calls answered delay field in this group

I know im missing something simple

 

[lbass]

Maybe try adding in the "daily" again.

-LB