Date time difference!
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=5828
Printed Date: 04 May 2024 at 6:30am
Topic: Date time difference!
Posted By: snapper
Subject: Date time difference!
Date Posted: 19 Mar 2009 at 2:01pm
|
Hey guys!
This is probably pretty easy buy I cant figure it out.
I have a create date and time and a resolved date and time.
I need the difference in a "Days:Hours:Min" format. (I Managed to string together some date dif formulas to get the format)
The problem though is I need it to be able to be evaluated so I can produce a Min, Max, Avg.
Any help would be awesome!
Thanks
|
Replies:
Posted By: DBlank
Date Posted: 19 Mar 2009 at 7:07pm
|
Did you end of with a text string?
My initial thought for min and max is to just do a datediff on minutes and use that formula for those. FOr the average I think you could use the same thing but then convert it back into the format you want.
|
Posted By: lockwelle
Date Posted: 20 Mar 2009 at 6:48am
|
I wouldn't worry about the displayed value...initially. Do all of you calculations on your original fields then translate it for the display....
So for your Average it might be that you write a formula like:
totext(avgerage(field, group), "dd:hh:ss")
just drop the formula where desired. I would think that this would work...at least it might provide a starting point for the solution.
|
Posted By: snapper
Date Posted: 20 Mar 2009 at 10:06am
| I think my issues is I am tryin gto have a cross tab calculate the average and it doesn't know what its looking at. I think I need to do th ecalculation in the formula then post it to a table! Thanks for the tips guys! |
Posted By: snapper
Date Posted: 23 Mar 2009 at 1:50pm
| so I have been workign with this......how do I do a datediff then find the minimum in the same formula? |
Posted By: DBlank
Date Posted: 23 Mar 2009 at 2:08pm
|
Not sure what you mean by finding the minimum within the "same formula".
The formula is to find the difference between 2 items then use the Summary function on that formula to find the minimum with that grouping that you want or for the entire report.
Is this what you mean?
datediff in minutes as "MinutesDifference":
datediff("n",{table.DATE1},{table.DATE2})
Summaries for entire report (not group levels):
maximum( mailto:%7b@MinutesDifference - {@MinutesDifference )
Minimum ( mailto:%7b@MinutesDifference - {@MinutesDifference )
Average ( mailto:%7b@MinutesDifference - {@MinutesDifference )
|
Posted By: snapper
Date Posted: 23 Mar 2009 at 3:18pm
|
Thats what I have going on.......but I strung them togther as I need the min, max, and avg all evaluated together (I am working with HelpDesk Tickets). I dont need the minimum day from one ticket and the minimum hour from another in the same timecode.
I need the minimum combination of "dd:hh:mm".
DateDiff ("d", {HPD_HelpDesk.Create Time},{HPD_HelpDesk.Arrival Time})
& " " &":"& (DateDiff ("h", {HPD_HelpDesk.Create Time},{HPD_HelpDesk.Arrival Time}) & " " &":"& (DateDiff ("n", {HPD_HelpDesk.Create Time}.{HPD_HelpDesk.Arrival Time}) the above equals @timeresolved
then I find
Maximum(@timeresolved) //Works
Minimum (@timeresolved) //Works
Average (@timeresolved) //Doesnt work
thanks for your patience!
|
Posted By: lockwelle
Date Posted: 24 Mar 2009 at 6:13am
|
What DBlank is saying, I think, is that the difference in minutes is all that you need, it is just a number. It seems that you are getting stuck on the display of the number. The min and max work, because it is picking the largest and smallest values of a string, but you can't take the average of a string.
DateDiff is going to return a datetime, when you want to display the number, AFTER finding the min, max, or Average; use ToText with a formatting option to display information as you want to see it.
After all, days and hours are just an easier way to keep track of minutes (as minutes are of seconds), and they are numbers. Numbers are easy to transforms, strings, well they look pretty but you can't do math with them.
Hope this helped
|
Posted By: DBlank
Date Posted: 24 Mar 2009 at 6:32am
Sorry for the delayed response but you are exactly correct on my suggestion and why, lockwelle.
|
Posted By: snapper
Date Posted: 24 Mar 2009 at 10:33am
|
I got it now.
Thanks for the patience guys....I got stuck thinking one way.....
I appreciate your help.
|