Date and Time Diff that returns TIME aswell

[Steve95]

Hi All

does anyone known how to do a datediff or another formula on two datetime fields so the returned value not only calculates the days but also the "h,n,s"

Thanks in Advance

Shin

 

[bdreed35]

To do what you want, do a datediff in seconds, then convert it back to day, hours, minutes, and seconds.

~Brian

 

[Steve95]

I have 2 fields 'startdate' and 'enddate' both in datetime data type now what Iam trying to achieve is the difference between the 2 dates and ALSO the hours and minutes

Yeah I can do the days easily:

datediff ('d', {startdatefield}, {enddatefield})

but i dont know how to retrieve 'h' and 'm' aswell as the days?

Cheers

 

[Steve95]

when you say convert it back, in which way? I have tried rhs click on field and 'format field' no joy.

 

[campsys]

Shinda,

This formula will give you the remaining Hours,Minutes,Seconds.

DateTime(0,0,0) + ({enddatefield} - {startdatefield})

After creating this formula, you need to surpress the default AM/PM indicator on the field. Use the Format Editor to choose 24 Hour time display in Hr:Min:Sec format.
This should give you time since midnight of the remaining day.

You can then combine the #Days and Time Remaining formulas into one field.

 

[bdreed35]

Here is code from synapsevampires FAQ faq767-3543 that I have modified slightly:

numberVar dur := datediff("s",{Orders.Order Date},currentdate); //get the seconds between 2 dates
numbervar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

days:= truncate(truncate(truncate(dur/60)/60)/24);
hrs := remainder(truncate(truncate(dur/60)/60),24);
min := remainder(truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(int(days),"0") + " day " + totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");

hhmmss

~Brian

 

[Steve95]

Cheers guys i appreciate the help and quick reponse.........


Campsys i tried your formula but I could not get the results I needed. Cheers all the same......

bdreed35 and synapsevampires many thanks for that formula an absolute beauti......hit the nail on the head! Many Thanks!

Cheers guys have a nice festive period.....