Calculate minutes between range

[Bijaz]

I'm interested in calculating in room time (this is for an operating room) between 5 pm and 7 pm. We're trying to figure out how often we use 12 ORs between these two time. Currently I have a column with "in room time" and "out of room time" . I want to know how many minutes a patient was in the room during that 5-7 pm window. Is there a way to do this?

Here is what I would like it to look like[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Surgery date[/TD]
[TD]In Room Time[/TD]
[TD]OUt of Room Time[/TD]
[TD]Minutes between 5-7 pm[/TD]
[/TR]
[TR]
[TD]7/1/2013[/TD]
[TD]0700[/TD]
[TD]1659[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7/1/2013[/TD]
[TD]1500[/TD]
[TD]1830[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]7/1/2013[/TD]
[TD]1820[/TD]
[TD]2100[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]7/1/2013[/TD]
[TD]1859[/TD]
[TD]2130[/TD]
[TD]1[/TD]
[/TR]
</TBODY>[/TABLE]

 

[BrianMH]

My formula works for those instances on my machine.

Sheet1

*	A	B	C	D
Date	In	Out	5-7 minutes

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:75px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]30/08/2013[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]16:59[/TD]
[TD="align: right"]00[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]31/08/2013[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]18:30[/TD]
[TD="align: right"]90[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]01/09/2013[/TD]
[TD="align: right"]13:51[/TD]
[TD="align: right"]21:19[/TD]
[TD="align: right"]120[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]02/09/2013[/TD]
[TD="align: right"]18:40[/TD]
[TD="align: right"]20:13[/TD]
[TD="align: right"]20[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =IF(C2>TIMEVALUE("17:00"),(IF(C2<=TIMEVALUE("19:00"),C2,TIMEVALUE("19:00"))-IF(B2>=TIMEVALUE("17:00"),B2,TIMEVALUE("17:00"))),0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Bijaz]

I'll give that a try later today

 

[Bijaz]

p.s. please try to be clear to which solution you are refering to in your replies.[/QUOTE]

GOt it. I will try yours today.

 

[Bijaz]

Does this work for you?

Sheet1

	A	B	C	D
Surgery date	In Room Time	OUt of Room Time	Minutes between 5-7 pm

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 82px"><COL style="WIDTH: 64px"><COL style="WIDTH: 71px"><COL style="WIDTH: 70px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]07/01/2013[/TD]
[TD="align: right"]07:00[/TD]
[TD="align: right"]16:59[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]07/01/2013[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]18:30[/TD]
[TD="align: right"]90[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]07/01/2013[/TD]
[TD="align: right"]18:20[/TD]
[TD="align: right"]21:00[/TD]
[TD="align: right"]40[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]07/01/2013[/TD]
[TD="align: right"]18:59[/TD]
[TD="align: right"]21:30[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]07/01/2013[/TD]
[TD="align: right"]13:51[/TD]
[TD="align: right"]21:19[/TD]
[TD="align: right"]120[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]07/01/2013[/TD]
[TD="align: right"]18:40[/TD]
[TD="align: right"]20:13[/TD]
[TD="align: right"]20[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula D2 =MAX(MIN(MOD(C2,1),"19:00:00")-MAX(MOD(B2,1),"17:00:00"),0) <TBODY> </TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

p.s. please try to be clear to which solution you are refering to in your replies.

Worked like a charm! Thanks!