Allocate Time Range over each hour of the day?

[Bill Elerding]

Greatings from the SF Bay Area. I'm lost on a particular problem.

I have patients that are in and out of different departments like ED and
Clinics. I'd like to take an arrival-to-discharge time, and spread the
minutes/hours they were 'in-department' during the day. For example, Patient
'A' presents at 14:15 and is discharged at 16:25. I'd like to allocate his
time as .75 hours (or 45 monutes) between 14:00 and 14:59, 1 hour between
15:00 and 15:59, and .42 hours (25") between 16:00 and 16:59. I haven't a
clue how to efficiently do this. Thanks in advance for help on this...
hopefully it will shorten waits in the Emergency Room.

I envision a spreadsheet delineating Hour of Day on the top row, and
patients down column 'A'. Each patient stay would be noted as hours or
minutes in a row over the appropriate hour columns. -Bill

 

[Satti Charvak]

Assuming timein data is in cell b2 and timeout data is in cell c2, i have
prepared a formula for you:

=IF(HOUR(B2)=14,IF(HOUR(C2)=14,(MINUTE(C2)-MINUTE(B2))/60,(60-MINUTE(B2))/60),"")

this formula calulates time between 14:00 and 14:59 hours, just copy this
formula to different time intervals and just change 14 to the hour interval
you want say 10, 15...etc

also note that the number 14 comes 2 times in the formula so the change
also has to be done 2 times