substract one datetime field from another

[jtammyg]

Hi!

I have a column called message and another column called delivered.

MESSAGE DELIVERED
200205281618 200205290700
200205281908 200205301920

These 2 fields are in the format yyyymmddhhmm. They are strings.

I want to be able to convert them to DD:HH:MM:SS and then

substract DELIVERED FROM MESSAGE in the format DD:HH:MM:SS.

Any ideas on how to do this?

Thanks a lot!!!

 

[synapsevampire]

Here's a standard formula tweaked slightly to supply your data, this should all be in one formula:

whileprintingrecords;
stringvar MyDate1 :="200101101234";
stringvar MyDate2 :="211001101234";
Datetimevar DT1;
Datetimevar DT2;
numbervar SecondsDiff;
DT1 := datetime(val(left(MyDate1,4)),val(mid(MyDate1,4,2)),val(mid(MyDate1,6,2)),val(mid(MyDate1,8,2)),val(mid(MyDate1,10,2)),0);
DT2 := datetime(val(left(MyDate2,4)),val(mid(MyDate2,4,2)),val(mid(MyDate2,6,2)),val(mid(MyDate2,8,2)),val(mid(MyDate2,10,2)),0);
SecondsDiff := datediff("s",DT1,DT2);


// Do the display
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;
hrs := Truncate(Truncate(SecondsDiff/60)/60);
min := Remainder(Truncate(SecondsDiff/60),60);
sec := Remainder(SecondsDiff,60);
hhmmss := totext(hrs, "0&quot + ":" + totext(min, "00&quot + ":" + totext(sec, "00"

hhmmss

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[jtammyg]

Hi!

It gives an error that hours must be between 0 and 23.

thanks!

 

[synapsevampire]

That's suggesting that your data contains something other than the format you described.

Create a formula to check for bad data:

if val(mid(MyDate2,8,2)) > 23
or
val(mid(MyDate2,8,2)) < 0
or
val(mid(MyDate1,8,2)) > 23
or
val(mid(MyDate1,8,2)) < 0 then
totext(val(mid(MyDate2,8,2)),0)
else
&quot;looks OK&quot;

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Crystalfire]

How would this formula change when I have MM/DD/YY HH:MM:SS PM I need to show the difference between a Punch in time ( One Date Field ) and Punch out date ( another Date Field). Thanks!!

 

[jtammyg]

Is there any other way of doing this?

Thanks!