Calculating difference between date & time stamps
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17776
Printed Date: 03 May 2024 at 2:44pm
Topic: Calculating difference between date & time stamps
Posted By: Steve Davies
Subject: Calculating difference between date & time stamps
Date Posted: 16 Oct 2012 at 6:13am
I wonder if anyone can help me.
I have designed a report and the only reference to time spent is captured from the date & time stamp log. So I have tracked someone logging an activity as follows on the table custom_log.DateCreated.
14.53.02 11/10/2012
14.53.06 11/10.2012
14.53.17 11/10/2012
14.53.54 11/10/2012
14.53.54 11/10/2012
What I need to work out is how to calculate the difference in time when they first logged something at 14.53.02 and when they finished at 14.53.54
If anyone can point me in the right direction that will be brilliant. Thanks
Steve
|
Replies:
Posted By: lockwelle
Date Posted: 16 Oct 2012 at 8:23am
|
datediff("m", {table.field}, next({table.field})) or something very similar will give the number of minutes between the 2 datetimes. There are other options for the timespan...check help for other options. Also as a caveat, Next doesn't respect group borders, so you might want to suppress the result if the value is negative Just as a thought, you could capture the start value in a shared variable in the group header and then compare it to the ending value in the group footer to get around the NEXT() issue are to cut down on the calculations if they aren't needed. HTH
|
Posted By: shanth
Date Posted: 16 Oct 2012 at 8:40am
Also You can Try,
Convert your field to String, if not, then:
Create Formula Formula1 : DateTime (YYYY, MM, DD, HH, MM, SS)
In your case:
DateTime ( ToNumber (right ({@FIELD},4)),
ToNumber (Mid ({@FIELD},13,2)),
ToNumber (Mid ({@FIELD},10,2)),
ToNumber (LEFT ({@FIELD},2)),
ToNumber (Mid ({@FIELD},4,2)),
ToNumber (Mid ({@FIELD},7,2)) )
Then use lockwelle's Formula :
DateDiff ("s",maximum({@Formula1}), minimum({@Formula1})) / 3600;
|
Posted By: Steve Davies
Date Posted: 16 Oct 2012 at 10:27pm
Many thanks for your response but when i apply the formula it come up as 0.00 and I am wondering if this is because its counting minutes. I have changed the "m" to "s" and i appear to be getting seconds now so my next question is how to I convert seconds into minutes and seconds.
Many thank for your help.
|
Posted By: Steve Davies
Date Posted: 16 Oct 2012 at 10:39pm
thank so much for your reply but I have to be honest, this blew my sock off and went over me head.
I have achieved much with datediff("s", {custom_Log.DateCreated}, next({custom_Log.DateCreated})) but now I want to now how to convert seconds into "m" & "s" so if you can help me with that I will be most grateful.
Regards,
Steve
|
Posted By: lockwelle
Date Posted: 17 Oct 2012 at 2:06pm
|
local numbervar sec; local numbervar l; local numbervar r; sec := datediff();// you can plug this in...so now you have secs l:=cint(sec/60); r:=sec mod 60; totext(l,0,"") + right("00" + totext(r,0,""),2); should work to return a string as m:ss HTH
|
|