Time difference

[Former Member]

Don't know if one of you can help me with this calculation in crystal report.

I have two time fields

Field 1 (type: Number) : 1459

Field 2 (type: Number):  1503

I need a time difference in hours and minutes: 0 hours and 4 minutes

Seems simple but I can't find a formula which would work.

[abhilash_kumar · Accepted solution]

Hi Rameez,

What do the numbers in these two fields means?

Are they seconds or minutes?

-Abhilash

[Former Member · Accepted solution]

Hi Abilash,

The 14 is the hour of a 24 hour period

and 59 is the minues of the 60 minute period

[abhilash_kumar · Accepted solution]

Hi Rameez,

Create a formula with this code:

local numbervar x1 := {field1};

local numbervar x2 := {field2};

local numbervar sec := ctime(tonumber(totext(x2,'#')[1 to 2]),tonumber(totext(x2,'#')[3 to 4]),00) -

ctime(tonumber(totext(x1,'#')[1 to 2]),tonumber(totext(x1,'#')[3 to 4]),00);

local NumberVar Hours   := Truncate (sec/3600);

local NumberVar Minutes := Truncate (Remainder (sec,3600)/60);

Totext (Hours,0,"") + ' Hours and ' + Totext (Minutes,0,"") + ' Minutes';

-Abhilash

[Former Member · Accepted solution]

Brilliant. This works.

I have just realised I also need to look at days as well.

So if I have Field1 = 25/01/2016 14:59

and            Field2 = 27/01/2016 15:03

so the output can be 2 days 0 Hours 4 minutes ?

[abhilash_kumar · Accepted solution]

If you have a DateTime Field, you can try:

datetimevar x1 := {datetime_field1};

datetimevar x2 := {datetime_field2};

numbervar sec := DateDiff('s', x1, x2);

NumberVar Days := Truncate (sec / 86400); 

NumberVar Hours := Truncate (Remainder (sec, 86400) / 3600); 

NumberVar Minutes := Truncate (Remainder (sec, 3600) / 60); 

NumberVar Seconds := Remainder (sec, 60); 

(If Days > 1 then Totext(Days,0,'') & " Days " else if Days = 1 then Totext(Days,0,'') & " Day " else "")& 

(If Hours > 1 then Totext(Hours,0,'') & " Hours " else if Hours = 1 then Totext(Hours,0,'') & " Hour " else "")&

(If Minutes IN [0.1 to 1] OR Minutes > 1 then Totext(Minutes,0,'') & " Minutes " else if Minutes = 1 then Totext(Minutes,0,'') & " Minute " else ""); 

-Abhilash

[Former Member · Accepted solution]

That's truly brilliant Abhilash!! I have been working for hours to achieve this but seems you did that so easily.

Regards

Niraj

[Former Member]

Hi,

numbervar x:=ctime(_Time1)-ctime(_Time2);

numbervar _hmode := x mod 3600;

cstr(x\3600,0) +' Hrs '+ cstr(_hmode\60,0)+' Min' ;

Regards..

Arun