Print Page | Close Window

[RESOLVED] Convert Elapsed Time Field and Average

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22050
Printed Date: 03 May 2025 at 12:31pm


Topic: [RESOLVED] Convert Elapsed Time Field and Average
Posted By: KCowden
Subject: [RESOLVED] Convert Elapsed Time Field and Average
Date Posted: 10 Aug 2016 at 6:02am
I hope one day to be a contributor to this amazing site, but for now I'm the needy little kid that keeps showing up at the door.

I am working on a report for my department where I am trying to count the number of computer generated incidents and how long it took to get those runs assigned.

I have tried everything I can find on the net but I have not found the correct solution or I'm simply too ignorant to make them work.

The data field {Response_Master_Incident.Elapsed_InQueue_2_FirstAssign} is a string (varchar(10), null). The format is hh:mm:ss but is not an integer field.

I am hoping there is a way I can convert the field into an integer and then add average summaries to all group sections.

Any help would be greatly appreciated.

Thank you,
Kyle

-------------
There is no situation so terrible that you can't make it worse.


Replies:
Posted By: kevlray
Date Posted: 10 Aug 2016 at 8:17am
I would think converting the string to a number with a formula (i.e. val(left( {Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},2))*3600 +val(mid({Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},3,2))*60 +val(right({Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},2))  )at least it should be a number (in seconds).

Posted By: KCowden
Date Posted: 12 Aug 2016 at 9:29am
Thank you but I'm missing something.

It displays the trailing seconds only. I.E. Original Value is "00:01:13" the displayed value in the formula field "13".

Is there maybe a way to convert the value to integers either when the data is pulled in or after writing to the report?

-------------
There is no situation so terrible that you can't make it worse.

Posted By: kevlray
Date Posted: 12 Aug 2016 at 1:01pm
I did not test the code.  I miss counted for the mid.
val(left( {Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},2))*3600 +val(mid({Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},4,2))*60 +val(right({Response_Master_Incident.Elapsed_InQueue_2_FirstAssign},2)) 

Posted By: KCowden
Date Posted: 16 Aug 2016 at 2:05am
Okay! Now we're simmering. Thank you very much, it works, gives me seconds and I can apply average summaries.

Now if I can beg one more favor. Is there a way I can get it to display as hh:mm:ss? My first thought is to repeat your formula using the "/" operator vs. the "*" (multiplication) operator. But I am so beyond ignorant on how CR thinks.

Help?..Please?

God Bless,
Kyle

-------------
There is no situation so terrible that you can't make it worse.

Posted By: kevlray
Date Posted: 16 Aug 2016 at 4:47am
But I thought the original value was in hh:mm:ss?!?  Thus you should be able to just drop that field in where you need it.

Posted By: KCowden
Date Posted: 19 Aug 2016 at 1:19am
The original field was displayed as hh:mm:ss but it was a string (varchar) not integers.

Your solution does convert the field to integers as seconds and allows me to average by seconds but I was hoping to get the integer field (seconds) to display as hh:mm:ss or the average summary to display as hh:mm:ss.

With the first, I could replace the elapsed time field and average off of that. With the second I could average off of the formula field without including that field in the actual report.

You've gotten me a lot closer to a solution but my ignorance has outpaced the answers.

Added in Edit: A thought occurred to me. In the data set I have available two date/time fields. One is when the run enters the queue and one when the first unit is assigned. That time difference equals the Elapsed_InQueue_2_FirstAssign value.

I tried to use a DateDiff function but I cannot get the value to display as hh:mm:ss. What I get is a single integer 0-5 based on how many minutes are between the two times.

If I get to that point I can run the average summary from that value.

-------------
There is no situation so terrible that you can't make it worse.

Posted By: KCowden
Date Posted: 22 Aug 2016 at 3:13am
Thank you @kevlray for your help! With what you gave me and some other things I've read that were tangentially related, it sorted itself out in a dream (I know sad, right?).

The first thing I did was use a built in TimeDate function to calculate the elapsed time instead of pulling the text string of the elapsed time by using the Time() function in my formula.

I used the DateTime that the first unit was assigned and subtracted the DateTime that the call entered the queue which gave me the total seconds. 

Time({Response_Master_Incident.Time_First_Unit_Assigned})  - Time({Response_Master_Incident.Time_CallEnteredQueue})


Then I used a string conversion formula as suggested by @DBlank in another thread to format it to display correctly (Display String in Format Field Dialog): 

local numbervar RemainingSeconds;

local numbervar Hours ;

local numbervar Minutes;

local numbervar Seconds;

 

//divide the @TotalSeconds by 3600 to calculate

// hours. Use truncate to remove the decimal portion.

Hours := truncate(currentfieldvalue / 3600);

// Subtract the hours portion to get RemainingSeconds

RemainingSeconds := currentfieldvalue - (Hours *

3600);

// Divide RemainingSeconds by 60 to get minutes.

// Use truncate to remove the decimal portion.

Minutes := truncate(RemainingSeconds/60);

// Subtract the Hours and Minutes and what is left

// over is seconds.

Seconds := currentfieldvalue - (Hours * 3600) -

(Minutes * 60);

// Format the hours, minutes, and seconds to hh:mm:ss

totext(Hours,"00") + ":" + totext(Minutes,"00") +

":" + totext(Seconds,"00")


And now I can average, total, determine max and min values. Thank you so much for your patience and thank you for making this forum what it is.

-------------
There is no situation so terrible that you can't make it worse.


Print Page | Close Window