• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Calculating a Time in Crystal Reports

 5 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
JeanneS
Veteran Member
Posts: 49
Veteran Member
12/2/2010 6:19 PM
How do you calculate a time when the value is stored as a number?  For example, I want to calculate the time difference between WHSHLDTL.UPDATE_TIME and WHSHIPLINE.UPDATE_TIME.  Both fields are stored as a number. 
Thanks in advance!
Ruma Malhotra
Veteran Member
Posts: 412
Veteran Member
12/3/2010 3:17 PM
DateDiff is a function that is present to calculate the difference in time. You can explore different options in this function to present the difference in sdays, months etc.
JeanneS
Veteran Member
Posts: 49
Veteran Member
12/7/2010 11:54 AM
I tried to use DateDiff but since the time field is formatted as a number I get an error. Any suggestions as to how to convert the time from a number to a time? Thanks!
mark.cook
Veteran Member
Posts: 444
Veteran Member
12/7/2010 12:15 PM
It has been a while since trying this but take a look to see if you can convert the time using the formula either IsTime(number) or TimeValue(number) then once converted you might be able to get the difference.

John Henley
Posts: 3355
12/7/2010 12:56 PM
Time is generally stored in Lawson as HHMMSS in a long integer. So you need to combine 1) convert to text, 2) extract HH, MM, and SS separately, recombine into a Crystal Time variable. Off the top of my head, it's something like this:
time(left(totext({field},"000000"),2),mid(totext({field},"000000"),3,4),right(totext({field},"000000"),2))=
Thanks for using the LawsonGuru.com forums!
John
JeanneS
Veteran Member
Posts: 49
Veteran Member
12/7/2010 6:55 PM
Thank you. I was able to get the time calculation.