Need help with a spreadsheet calculation

[Narkos]

I'm trying to figure out how to calculate the number of minutes a patient is in the OR (in room to out of room) between 0700 and 1500. Some rooms run past 1500, but don't want to count that time. Here's a youtube video showing my dilemma. Can anyone help me figure this out?

[Duer 157099]

1. Need clarification

Sorry, I didn't have sound when I watched, so maybe this was specified, but anyway, do you mean: if the end time exceeds 1500, then just subtract the amount of time that is exceeded, or don't count that entry at all?

Do you know how to use logical functions, the IF/THEN type? Like, IF (cell value exceeds certain number) THEN (subtract certain value)?

[Narkos]

2. Exactly

I want to know total in room minutes, but yes, if it exceeds 3 pm I would want that time subtracted. I'm familiar with some of the functions, but no where near where I need to be.

[dmallind]

3. Easiest way is with min

Enter the time 15.00 in the appropriate format anywhere on the workbook. Say cell W1

Then to get time for your first patient visit use in cell F4

=MIN($W$1,E4)-D4

then fill down the column to get the others. The dollar signs fix the reference cell to 15.00 and are easier than entering it in time format.

So if a patient comes in at 14.00 and leaves at either 15.00 or 17.30 you still get one hour.

Time/date calculations are format sensitive so make sure the F column is formatted to give you a number.

[Duer 157099]

4. This way is good too

the MIN refers to minimum, or the lesser of (whatever, in this case 15 or E4).

But converting the time to a number must be done in any case. Once that happens, it should be easy.

[Narkos]

5. Thanks...will try when I get home n/t

[Narkos]

6. Wow, that was perfect....thanks!!!!!!