Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Calculating DateTime Diff Post Reply Post New Topic
Author Message
jas1503
Newbie
Newbie
Avatar

Joined: 20 Feb 2008
Online Status: Offline
Posts: 3
Quote jas1503 Replybullet Topic: Calculating DateTime Diff
     Posted: 27 May 2008 at 11:00pm
Hi CR experts.

I have created two fields (TIME IN and TIME OUT) out of a single field "GENTIME". I made a cross stab query to show the Minimum of TIME IN and the Maximum of TIME OUT on each day. Now, I need to compute the date/time difference for each day of Minimum TIME IN and Maximum TIME OUT in Hour:Min:Sec. format.

                            Date                 Time In         Time Out     Time Diff
Employee Name   05/19/2008      7:04 am        5:23 pm     00:00:00
                            05/20/2008     6:39 am         5:14 pm     00:00:00
                            05/21/2008      7:23 am        5:09 pm     00:00:00
 
Can anyone please show me the correct formula to get the difference?

Thank you in advance.

Cheers!

Jas
IP IP Logged
jas1503
Newbie
Newbie
Avatar

Joined: 20 Feb 2008
Online Status: Offline
Posts: 3
Quote jas1503 Replybullet Posted: 29 May 2008 at 4:10pm

Anyone please?

I just need to calculate the TIME DIFF for the MAXIMUM Time of TIME OUT less the MINIMUM Time of TIME IN for EACH DAY in a CROSS TAB QUERY.
 
Thank you in advance.
 
Cheers!
 
Jas
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 12 Jun 2008 at 9:48pm
It's a bit tricky and I cover it in great detail in my book (with a full example). I also have answered this elsewhere in more detail in other forum posts. So you can search the forum and find it somewhere in here. But here is the general idea.

You need to create two Global arrays in a formula and attach that formula to some non-necessary property of the cross-tab. In the book's example I used CSS Class Name property. Then in the formula you need to pull the data from the cross-tab as it is being generated and save it to the array. You can get the current value using CurrentFieldValue. Thus, you can save the Time In to the first array and Time Out to the second array.

The next problem is determing when the cross-tab is printing the first column or the second column. Use the function GridRowColumnValue() to get this data. Use it with an If-Then statement to decide which array to put it in.

yes, this is pretty complicated (it takes me seven pages to cover it in the book). But if you are experienced with formulas, you should be able to pull it off.

You can find out more about my books at Amazon.com or reading the Crystal Reports eBooks online.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum