Calculating a Time in Crystal Reports

Name	Points
Greg Moeller	4184
David Williams	3349
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1377
Roger French	1315
mark.cook	1244
Chris Martin	825

JeanneS

Veteran Member

Posts: 49

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

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

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

New Poster

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: 3366

New Poster

Engaged Reader

Avid Reader

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))=

JeanneS

Veteran Member

Posts: 49

12/7/2010 6:55 PM

Thank you. I was able to get the time calculation.