Hi all, I'm struggling with a formula to count the hours of a time span. I have patient data that list the time their surgery starts and the time it ends. Example: Procedure start time = 06/01/2017 14:00 Procedure stop = 06/01/2017 16:23.
What I am looking to do is graph the count of each hour this patient was in surgery, meaning the example patient above was in the OR room on the 14th hour, 15th hour and the 16th hour. I want to count this for all patients in my list and display a total count of patient in surgery at a given hour.
My original solution was to create a column for each hour in the day - 0:00 1:00 2:00 ...22:00 23:00. Then in each column I would compare the header description such as 2:00 to the (Hour) of the start and stop time, if it was greater then the start or less than the stop, it would return a "1" value. =IF(AND(T$1>=$M4,T$1<=$N4),1,0) However, this does not work for Midnight as 0 hour is smaller than 23 hour, yet in reality it is larger in time.
Example data is below, where I also created Procedure Start and Stop Hour fields, but I think I might be making it too complicated.
Any suggestion would be greatly appreciated.
Terry
<tbody>
</tbody>
What I am looking to do is graph the count of each hour this patient was in surgery, meaning the example patient above was in the OR room on the 14th hour, 15th hour and the 16th hour. I want to count this for all patients in my list and display a total count of patient in surgery at a given hour.
My original solution was to create a column for each hour in the day - 0:00 1:00 2:00 ...22:00 23:00. Then in each column I would compare the header description such as 2:00 to the (Hour) of the start and stop time, if it was greater then the start or less than the stop, it would return a "1" value. =IF(AND(T$1>=$M4,T$1<=$N4),1,0) However, this does not work for Midnight as 0 hour is smaller than 23 hour, yet in reality it is larger in time.
Example data is below, where I also created Procedure Start and Stop Hour fields, but I think I might be making it too complicated.
Any suggestion would be greatly appreciated.
Terry
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | Procedure Start Day | Q | R | Sat | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ |
Case ID | Patient Class | HSP Account ID | Month and Year | Procedure Start | Procedure Complete | Room Cleanup Start | Room Cleanup Complete | Room | Location | Primary Surgeon ID | Primary Surgeon Name | Procedure Start Hour | Procedure Complete Hour | Procedure Start Month | Procedure Start Day | Procedure Start Year | Duration | Day of Week | 0:00 | 1:00 | 2:00 | 3:00 | 4:00 | 5:00 | 6:00 | 7:00 | 8:00 | 9:00 | 10:00 | 11:00 | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 | 21:00 | 22:00 | 23:00 |
100965 | Emergency | 12 | 02/01/2017 0:00 | 02/22/2017 20:08 | 02/22/2017 20:52 | CATH LAB 4 | Cath Lab Location | 4125 | Todd | 20:00 | 20:00 | 2 | 22 | 2017 | 0:44 | Wed | 1 | |||||||||||||||||||||||||
101542 | Emergency | 13 | 02/01/2017 0:00 | 02/23/2017 18:51 | 02/23/2017 19:51 | CATH LAB 4 | Cath Lab Location | 6966 | Bob | 18:00 | 19:00 | 2 | 23 | 2017 | 1:00 | Thu | 1 | 1 | ||||||||||||||||||||||||
101110 | Emergency | 14 | 02/01/2017 0:00 | 02/23/2017 12:54 | 02/23/2017 14:18 | OR 07 | Main OR | 7849 | Mary | 12:00 | 14:00 | 2 | 23 | 2017 | 1:24 | Thu | 1 | 1 | 1 | |||||||||||||||||||||||
101351 | Emergency | 15 | 02/01/2017 0:00 | OR 07 | Main OR | E1082 | Todd | 0:00 | ||||||||||||||||||||||||||||||||||
101526 | Emergency | 16 | 02/01/2017 0:00 | 02/23/2017 17:38 | 02/23/2017 17:58 | CATH LAB 4 | Cath Lab Location | 6424 | Bob | 17:00 | 17:00 | 2 | 23 | 2017 | 0:20 | Thu | 1 | |||||||||||||||||||||||||
101348 | Emergency | 17 | 02/01/2017 0:00 | 02/23/2017 13:12 | 02/23/2017 13:42 | OR 10 TRAUMA | Main OR | 7849 | Mary | 13:00 | 13:00 | 2 | 23 | 2017 | 0:30 | Thu | 1 | |||||||||||||||||||||||||
102092 | Emergency | 18 | 02/01/2017 0:00 | 02/25/2017 5:15 | 02/25/2017 6:32 | CATH LAB 2 | Cath Lab Location | 6424 | Todd | 5:00 | 6:00 | 2 | 25 | 2017 | 1:17 | Sat | 1 | 1 | ||||||||||||||||||||||||
103515 | Emergency | 19 | 03/01/2017 0:00 | OR 07 | Main OR | E1082 | Todd | 0:00 | ||||||||||||||||||||||||||||||||||
10356 | Emergency | 20 | 05/01/2016 0:00 | 05/20/2016 18:34 | 05/20/2016 19:23 | CATH LAB 2 | Cath Lab Location | 4125 | Bob | 18:00 | 19:00 | 5 | 20 | 2016 | 0:49 | Fri | 1 | 1 | ||||||||||||||||||||||||
105555 | Emergency | 21 | 03/01/2017 0:00 | 03/06/2017 0:33 | 03/06/2017 0:53 | CATH LAB 2 | Cath Lab Location | 4125 | Mary | 0:00 | 0:00 | 3 | 6 | 2017 | 0:20 | Mon | 1 |
<tbody>
</tbody>