Negative elapsed time calculating over midnight 2

[cynmar58]

I've searched and read threads, FAQ's, etc and probably still missed the fix for this. bdreed35's post to Thread 767-815497 worked perfectly to calculate and format an elapsed time between two 4 char string fields expressing military time for me (with my date fields being separate). However, I still get a negative elapsed time when the date/time changes over midnight. ie Date In= 5/1/04, Date Out=5/2/04, Time In=2300, Time Out=0100, Elapsed Time= -22:00. I'm using Crystal 8.5 with a KB_SQL database in a hospital calculating patient elapsed times in the ER.
Thanks so much, this is my first day using Tek-Tips, and already made progress!

 

[vidru]

Here's a formula that should give you a nudge in the right direction. This assumes (silly, I know, but since you didn't supply data types for DateIn and DateOut, I had to guess) that your date fields are also strings. If they aren't, then just nix the CDate() function from lines 3 and 4:
[tt]
StringVar Time1 := {Table.TimeIn};
StringVar Time2 := {Table.TimeOut};
DateVar DateIn:= CDate({Table.DateIn});
DateVar DateOut:= CDate({Table.DateOut});

TimeVar TimeIn:= Time(Val(Time1[1 to 2]), Val(Time1[3 to 4]),00);
TimeVar TimeOut:= Time(Val(Time2[1 to 2]), Val(Time2[3 to 4]),00);

Replace(ToText(DateDiff("n", DateTime(DateIn,TimeIn), DateTime(DateOut,TimeOut)) / 60, 2,""),".",":");
[/tt]
-dave

 

[synapsevampire]

Dave: Doesn't that just return the number of seconds (could have just used the datediff(s,,))?

Try:

whileprintingrecords;
Stringvar timein := {table.timein};
Stringvar timeout := {table.timeout};
numbervar Dur := datediff("s",datetimevar Datein := datetime(year({table.datein}),month({table.datein}),day({table.datein}),val(left(timein,2)),val(mid(timein,3,2)),0),
datetime(year({table.dateout}),month({table.dateout}),day({table.dateout}),val(left(timeout,2)),val(mid(timeout,3,2)),0));
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

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

hhmmss

-k

 

[vidru]

k,

It's actually getting the minutes, then dividing by 60 to turn it into hours.

-dave

 

[synapsevampire]

Dave: Oh right, divide by 60...

I assumed that the requirements would be minutes, perhaps not.

-k

 

[cynmar58]

Thank you both so much. You are correct, I should have supplied the fact that my date fields are in fact "date" fields..easy fix from your formula though. I needed hours and minutes, and got that from the second formula, just deleted the code for "seconds" for a neater final format. Works great! Thanks again, you've put me out of my misery.

-cindy