Need to Calc Time Broken Down in Shifts

[EricRN]

Hi,

I am working on a report that will break down the time of a surgery into shifts. I need a way to calculate the amount of time spent in each shift for a start datetime and end datetime. For example, if I have a surgery that starts at 0730 and ends at 1330 then the elapsed time for day shift is 360 minutes. If it started at 1430 and ended at 1530 then there would be 30 minutes for day shift and 30 minutes for evening shift. Also, if one started at 2330 and ended at 0430, the the evening shift would show 29 minutes and the day shift would show 270 minutes. There may also be times when a surgery could cross into all 3 shifts.

Any help would be greatly appreciated.

Eric

 

[synapsevampire]

It sounds like you'll need 3 formulas, but it would help if you'd supply example data, inclusive of data types.

The theory is that you'd identify each shifts hours in the formula with time variables, then determine if the dates are within those values, and if so, use datediff to determine the number of minutes.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[EricRN]

I tried to do the datediff function but the data was funky. I ended up trying the ctime function. Here's the formula I was working on:

timeVar cstart :=ctime({casemain.actcase_start_datetime}) ;
timevar cstop :=ctime({casemain.actcase_stop_datetime}) ;

if cstart > ctime (23,00,00) and cstart < ctime (7,00,00)
then timevar cstart:=ctime({casemain.actcase_start_datetime})
else timevar cstart:= ctime(23,00,00);
if cstop < ctime(23,00,00) and cstop > ctime(7,00,00)
then timevar cstop:= ctime(23,00,00)
else if (cstop > ctime(23,00,00) and cstop <= ctime(23,59,59)) and (cstart > ctime(00,00,00) and cstart < ctime(7,00,00))
then timevar cstop:= ctime({casemain.actcase_stop_datetime}) + 86400
else timevar cstop:= ctime({casemain.actcase_stop_datetime});
((cstop-cstart)/60)

I tried to define the shifts, but that wasn't working either. Here is how the report would look with a sample:

Surgery A starts at 1400 and ends at 2000.
Surgery B starts at 2200 and ends at 0300.
Surgery C starts at 0500 and ends at 0900.

1st shift is 0700 to 1500
2nd shift is 1500 to 2300
3rd shift is 2300 to 0700

Using the times from the above surgeries the report would look like this:

Day Shift Eve Shift Night Shift
180 min 360 min 300 min

This would be reported for weekdays, Saturday and Sunday. I will be using this information to figure out the OR utilization. Any ideas would be appreciated. I have been working on this for 3 weeks without luck.

Eric