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]

 

[FormR]

Maybe like this?

Excel Workbook
	A	B	C	D
1	Surgery date	In Room Time	OUt of Room Time	Minutes between 5-7 pm
2	07/01/2013	700	1659	0
3	07/01/2013	1500	1830	90
4	07/01/2013	1820	2100	40
5	07/01/2013	1859	2130	1
6	07/01/2013	1730	1845	75
Sheet1

 

[BrianMH]

Assuming that those values are stored as time then the following formula will work.
=IF(C2>TIMEVALUE("17:00"),(IF(C2<=TIMEVALUE("19:00"),C2,TIMEVALUE("19:00"))-IF(B2>=TIMEVALUE("17:00"),B2,TIMEVALUE("17:00"))),0)

Do a custom number format of [MM] on the result cell.

 

[Bijaz]

Wow, that worked perfectly! Thanks!

 

[Bijaz]

Self Delete

Not needed

 

[FormR]

Wow, that worked perfectly! Thanks!

Out of interest, which worked?

 

[Bijaz]

Just reviewed the data, and it looks like it works for some times but other it doesn't. For instance, for start and end times of 13:51 and 21:19, it returns a time 00:00, whereas with other times like 18:40(start) 20:13(end). Any ideas why?

 

[Bijaz]

Just tried yours, and it returned all zeros. I put he formula in exactly as you wrote it. Is there something else I should have done?

 

[Bijaz]

Just real quick, the times are formatted as such. Does that change anything?

 

[FormR]

Does this work for you?

Excel Workbook
	A	B	C	D
1	Surgery date	In Room Time	OUt of Room Time	Minutes between 5-7 pm
2	07/01/2013	07:00	16:59	0
3	07/01/2013	15:00	18:30	90
4	07/01/2013	18:20	21:00	40
5	07/01/2013	18:59	21:30	1
6	07/01/2013	13:51	21:19	120
7	07/01/2013	18:40	20:13	20
Sheet1

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