Sum Time Spent - Need help converting field
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13620
Printed Date: 29 Apr 2024 at 9:08am
Topic: Sum Time Spent - Need help converting field
Posted By: tdavenport
Subject: Sum Time Spent - Need help converting field
Date Posted: 28 Jun 2011 at 11:12am
|
I need help formating a field that will allow me to summarize time spent.
The database field field is formatted HH:MM. Techs enter their time in this format. Multiple entries can be made per CallID (PK).
When I group on CallID, I see all of the time entries. Example
Total Time
01:45
00:37
02:15
I want to add all of these times together to get a grand total of time spent per CallID.
When I attempt to summarize (looking for sum) on this field, I do not have any math summaries. All are text (Count, Max, etc...). I believe that is because this is a text field.
What can I do to format this field so that I can calculate the accumulative time? I'm assuming a formula will do it, but I can't seem to find the right formula or function that will convert this field into something that I can calculate.
Thank you!
------------- Any and all suggestions are welcome. |
Replies:
Posted By: DBlank
Date Posted: 28 Jun 2011 at 11:19am
|
you want to add total time, but how do you want it to display? in days, hours, minutes?
hours and minutes only?
|
Posted By: tdavenport
Date Posted: 28 Jun 2011 at 11:43am
|
Ideally in the same format HH:MM. But I'll take any format that is accurate. I can alway use Mid to reformat the result - if its a string.
------------- Any and all suggestions are welcome. |
Posted By: DBlank
Date Posted: 28 Jun 2011 at 11:56am
|
so you can convert the text to total minutes to add it up
//totalminutes as
tonumber(left(timefield,2))*60 + tonumber(right(timefield,2))
sum this field aat the call id group level
SUM(@totalminutes,table.callid)
gives you the total minutes for the call
assuming it does not go over 24 hours you can convert it into your format
as
dateadd('n',SUM(@totalminutes,table.callid),datetime(1900,1,1,0,0,0)) and then format this as military time HH:MM only
|
Posted By: tdavenport
Date Posted: 28 Jun 2011 at 12:16pm
|
The "ToNumber" worked beautifully. And yes, the times will stay under 24 hours - as this is simply a daily log.
I'm not sure what is happening with converting it back.
I created a second formula as follows:
dateadd('n',Sum ( mailto:%7b@Tonumber - {@ToNumber }, {CallLog.CallID})),datetime(1900,1,1,0,0,0))
but I get an error "The remaining text does not appear to be part of the formula" - this is referring to datetime and the remaining text to right.
What am I missing?
Thanks so much. ------------- Any and all suggestions are welcome. |
Posted By: tdavenport
Date Posted: 28 Jun 2011 at 12:25pm
|
Scratch that...put a ')' in the wrong place.....
That is perfect!!!!!
 Thank you! Saved me a ton of time tonight....
God I love this forum.
DBlank is DMan! ------------- Any and all suggestions are welcome. |