Calculating Time, but only to certain hour

[JonathanNYC]

Need help with a formula that calculates total operating room time used, but only time used up to 7 p.m. I derive time used by calculating the amount of time from “room open” to “patient out of room”. And, that formula works fine. However, there are some surgical cases that continue past 7 p.m. and management does not wish to see hours used past 7 p.m.

Here's an example: If the “open room” time is 5 pm and the “patient out of room” time is 8 p.m., I need the formula to generate a result of 2 hours (or 120 minutes), not 3 hours. I am using Crystal Reports 10.

 

[anglophobe]

Subtracting the two time from each other will give you the amount of time in second
{Time1}-{Time2}
(his assumes that your field is purely a time with no date part)

use:

If {Time2} > {Time(19,00,00) then
{Time(19,00,00) - {Time1}
Else
{Time2}-{Time1}

 

[synapsevampire]

You don't even bother to post what the data type is, so we can only guess.

The first thing you should post is the software being used (Crystal version and the database), then show example data, including data types, then show the expected output, then if you are so compelled, you can talk about the data and the requirements, examples are generally concrete.

Assuming that it's a datetime type, try creating a formula to user in lieu of the current datetime formula:

datetimevar Mytime:={table.outofroom};
timevar MaxTime:= time(19,0,0);
if time(MyTime) > MaxTime then
MyTime:=date(Mytime)+MaxTime;
MyTime

Now use the above formula in whatever calculations you have as it will always limit itself to 7PM as the maximum time.

-k

 

[JonathanNYC]

I'm close, but need an extra push.

The formula (I label the formula "upto7pm")is working fine.

Here's the Formula:
datetimevar Mytime:={pcmOpTime.timePatientLeaveRoom};
timevar MaxTime:= time(19,0,0);
if time(MyTime) > MaxTime then
MyTime:=date(Mytime)+MaxTime;
MyTime

patientleaveroom times that are past 19:00 hours are correctly stopping at 19:00 hours. For example, 20:45 is showing as 19;00.

I can't yet get my "room open" times to subtract from the "upto7pm" formula field though.

My "room open" field is:

(time({pcmOpTime.startSetup}))

I probably need to change the room open field to a datetime field (or the upto7pm formula to just a time field) and then do another calculation that subtracts "room open" from "upto7pm" but I can't quite get it.

I'm using CR10.

 

[lbass]

Try:

evaluateafter({@upto7pm});
datetimevar MyTime;
datediff("n",{pcmOpTime.startSetup},MyTime)

This should give you the difference in minutes.

-LB

 

[JonathanNYC]

Thank you Anglophobe, K and LB for your help and patience. I used elements of all your responses and it works perfectly; having incorporated LBs last suggestions this morning (and dividing by 60 to get hours). I am most appreciative to all of you.

 

[anglophobe]

If you change the "n" into "h" in lbass's suggestion, it will automatically go to hours.

 

[JonathanNYC]

Thanks again, anglophobe, It is often Murphy's Law with these reports. That is, one gets a telephone call indicating the President and CFO would like to see, etc. That is what transpired. Again, much thanks for you valuable help.