Formula for time calculation, counting the span of hours

T

tbobolz

Board Regular
Joined
Mar 18, 2010
Messages
144
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


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>
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
You may want to consider using 24:00 for midnight. If that's not an option, please give some examples in which the procedure completes at midnight.

In your table, there is one example in which the procedure started at 0:00 and completed at 0:00. Is it a 24 hour procedure?
 
Upvote 0
tbobolz, below are two formulas I worked out and which may be useful to you:

1. This one tells the exact number of hours the patient was in:

=(F2-E2)*24

2. This one tells the number of HOUR-BLOCKS the patent was in (even if they were only in for a single minute of that hour-block):

=ROUNDUP((MINUTE(E2)/60)+((F2-E2)*24),0)

For instance, if the patient went in on 2/22 at 11:58 PM and came out on 2/23 at 1:02 AM, these formulas would give the following results respectively:

1.06 (hours)

3 (time blocks: 2/22 - 23:00, 2/23 - 0:00, 2/23 - 1:00)
 
Upvote 0
What's not clear to me, Terry, is if a patient's "in-time" starts on, say, Wednesday in the 23:00 block and ends Thursday in the 1:00 block, that is TWO days at the patient was in (as far as your chart is concerned). But your chart only appears to account for ONE day per chart. So will patients who were in past midnight be entered TWICE: once for Wednesday and once for Thursday? Otherwise, how will you account for the fact that a Wednesday-in patient occupied a room Thursday during your 0:00 and 1:00 blocks?
 
Upvote 0
yky said:
You may want to consider using 24:00 for midnight. If that's not an option, please give some examples in which the procedure completes at midnight.

In your table, there is one example in which the procedure started at 0:00 and completed at 0:00. Is it a 24 hour procedure?
Click to expand...



Hi, thanks for your time!

I did try changing midnight to 24:00, however I still end up with the same situation. My formula is only looking at columns M and N and comparing to determine if the current hour value in the header is between those two times. If so, it assigns a 1 values and counts it. The problem is when midnight occurs and the midnight value (the complete hour) is "0", which is now smaller than the procedure start time.

Here's my formula and better data examples:

Formula in T2 is - =IF(OR($E2="",$F2=""),"",IF(AND(T$1>=$E2,T$1<=$F2),1,0))


I tried just using the procedure start and complete hour field in the formula, but still I am struggling. Maybe I am looking at it all wrong. I basically want to determine a count for each hour the patient is in a room. So if I had surgery from 1pm to 4pm, I would be in that room on the 1pm hour, the 2pm hour, the 3pm hour and the 4pm hour.

Thanks for any suggestion you can offer.

Terry



ABCDEFGHIJKLMNOP QRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
Case IDPatient ClassHSP Account ID (HAR)Month and YearProcedure StartProcedure CompleteRoom Cleanup StartRoom Cleanup CompleteRoomLocationPrimary Surgeon IDPrimary Surgeon NameProcedure Start HourProcedure Complete HourProcedure Start MonthProcedure Start DayProcedure Start YearDurationDay of Week0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
100965Emergency160078056002/01/2017 0:0002/22/2017 20:0802/22/2017 20:52 CATH LAB 4 Cath Lab Location4125Bob20:0020:0022220170:44Wed000000000000000000001000
101542Emergency160078462702/01/2017 0:0002/22/2017 20:0802/23/2017 1:51 Cath Lab Location6966Todd20:001:0022220175:43Wed000000000000000000000000
101110Emergency160078291802/01/2017 0:0002/23/2017 12:5402/23/2017 14:18 OR 07 Main OR7849Bob12:0014:0022320171:24Thu000000000000111000000000
101351Emergency160078384302/01/2017 0:00 Main ORE1082Todd0:00
101526Emergency160078457502/01/2017 0:0002/23/2017 17:3802/23/2017 17:58 Cath Lab Location6424Bob17:0017:0022320170:20Thu000000000000000001000000
101348Emergency160078384302/01/2017 0:0002/23/2017 13:42 OR 10 TRAUMA Main OR7849Todd13:0013:42
102092Emergency160079036402/01/2017 0:0002/25/2017 5:1502/25/2017 6:32 CATH LAB 2 Cath Lab Location6424Bob5:006:0022520171:17Sat000001100000000000000000
103515Emergency160080067503/01/2017 0:00 Main ORE1082Todd0:00
10356Emergency160005717905/01/2016 0:0005/20/2016 18:3405/21/2016 1:23 CATH LAB 2 Cath Lab Location4125Mike18:001:0052020166:49Fri000000000000000000000000
105555Emergency160081376903/01/2017 0:0003/06/2017 0:3303/06/2017 0:53 CATH LAB 2 Cath Lab Location4125Bob0:000:003620170:20Mon100000000000000000000000
103516Emergency160080067503/01/2017 0:0003/01/2017 7:5003/01/2017 9:15 OR 10 TRAUMA Main OR6913Todd7:009:003120171:25Wed000000011100000000000000
103991Emergency160080185403/01/2017 0:0003/01/2017 16:3703/01/2017 17:59 OR 10 TRAUMA Main OR6913Bob16:0017:003120171:22Wed000000000000000011000000
104653Emergency160080583403/01/2017 0:0003/02/2017 14:3403/02/2017 14:54 OR 10 TRAUMA Main OR7849Todd14:0014:003220170:20Thu000000000000001000000000
10783Emergency160005947205/01/2016 0:0005/23/2016 11:3205/24/2016 0:50 OR 04 Main OR7035Bob11:000:00523201613:18Mon100000000000000000000000

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col><col span="25"></colgroup>
 
Upvote 0
Hi Erik, Thanks for your time!

I will play around with your suggestion later today, but to answer your question, the change of day is where I am having trouble. Columns M through AQ are my own calculation, so maybe I am making this too difficult.

The problem is when midnight occurs and the midnight value (the complete hour) is "0", which is now smaller than the procedure start time.

Here's my formula and better data examples:

Formula in T2 is - =IF(OR($E2="",$F2=""),"",IF(AND(T$1>=$E2,T$1<=$F2),1,0))


I tried just using the procedure start and complete hour field in the formula, but still I am struggling. Maybe I am looking at it all wrong. I basically want to determine a count for each hour the patient is in a room regardless of the day count. So if I had surgery from 11pm to 1AM the next morning, I would be in that room on the 11pm hour, the 0am hour, and the 1am hour.

Thanks for any suggestion you can offer.

Terry



ABCDEFGHIJKLMNOP QRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
Case IDPatient ClassHSP Account ID (HAR)Month and YearProcedure StartProcedure CompleteRoom Cleanup StartRoom Cleanup CompleteRoomLocationPrimary Surgeon IDPrimary Surgeon NameProcedure Start HourProcedure Complete HourProcedure Start MonthProcedure Start DayProcedure Start YearDurationDay of Week0:001:002:003:004:005:006:007:008:009:0010:0011:0012:0013:0014:0015:0016:0017:0018:0019:0020:0021:0022:0023:00
100965Emergency160078056002/01/2017 0:0002/22/2017 20:0802/22/2017 20:52 CATH LAB 4 Cath Lab Location4125Bob20:0020:0022220170:44Wed000000000000000000001000
101542Emergency160078462702/01/2017 0:0002/22/2017 20:0802/23/2017 1:51 Cath Lab Location6966Todd20:001:0022220175:43Wed000000000000000000000000
101110Emergency160078291802/01/2017 0:0002/23/2017 12:5402/23/2017 14:18 OR 07 Main OR7849Bob12:0014:0022320171:24Thu000000000000111000000000
101351Emergency160078384302/01/2017 0:00 Main ORE1082Todd0:00
101526Emergency160078457502/01/2017 0:0002/23/2017 17:3802/23/2017 17:58 Cath Lab Location6424Bob17:0017:0022320170:20Thu000000000000000001000000
101348Emergency160078384302/01/2017 0:0002/23/2017 13:42 OR 10 TRAUMA Main OR7849Todd13:0013:42
102092Emergency160079036402/01/2017 0:0002/25/2017 5:1502/25/2017 6:32 CATH LAB 2 Cath Lab Location6424Bob5:006:0022520171:17Sat000001100000000000000000
103515Emergency160080067503/01/2017 0:00 Main ORE1082Todd0:00
10356Emergency160005717905/01/2016 0:0005/20/2016 18:3405/21/2016 1:23 CATH LAB 2 Cath Lab Location4125Mike18:001:0052020166:49Fri000000000000000000000000
105555Emergency160081376903/01/2017 0:0003/06/2017 0:3303/06/2017 0:53 CATH LAB 2 Cath Lab Location4125Bob0:000:003620170:20Mon100000000000000000000000
103516Emergency160080067503/01/2017 0:0003/01/2017 7:5003/01/2017 9:15 OR 10 TRAUMA Main OR6913Todd7:009:003120171:25Wed000000011100000000000000
103991Emergency160080185403/01/2017 0:0003/01/2017 16:3703/01/2017 17:59 OR 10 TRAUMA Main OR6913Bob16:0017:003120171:22Wed000000000000000011000000
104653Emergency160080583403/01/2017 0:0003/02/2017 14:3403/02/2017 14:54 OR 10 TRAUMA Main OR7849Todd14:0014:003220170:20Thu000000000000001000000000
10783Emergency160005947205/01/2016 0:0005/23/2016 11:32

<tbody>
</tbody>
 
Upvote 0
ErikTyler said:
tbobolz, below are two formulas I worked out and which may be useful to you:

1. This one tells the exact number of hours the patient was in:

=(F2-E2)*24

2. This one tells the number of HOUR-BLOCKS the patent was in (even if they were only in for a single minute of that hour-block):

=ROUNDUP((MINUTE(E2)/60)+((F2-E2)*24),0)

For instance, if the patient went in on 2/22 at 11:58 PM and came out on 2/23 at 1:02 AM, these formulas would give the following results respectively:

1.06 (hours)

3 (time blocks: 2/22 - 23:00, 2/23 - 0:00, 2/23 - 1:00)
Click to expand...



Okay, I now see where you are going with the "=ROUNDUP((MINUTE(E2)/60)+((F2-E2)*24),0)" formula, while this does identify the span of 3 time blocks, I need to identify which time block the patient is in, so I can ultimately graph it. In the end, all patients will display in the graph. In this case time blocks: 2/22 - 23:00, 2/23 - 0:00, 2/23 - 1:00. This is why my data has columns for 0:00 through 23:00, with a "1" count if the patient was in the room during the hour. This way I could simply create a graph by hour displaying occupancy.


Thanks for the time you have invested in helping me.

Terry
 
Upvote 0
Terry, it seems to me that if a patient is in the room past midnight, they'd simply need to be entered TWICE: once for date 1 and once for date 2. It's the only way I can see that you'll be able to keep ONE graph per ONE day.

So if a patient was in from 5/22 at 23:38 until 5:23 at 1:10, then for the 5/22 sheet, the patient would be listed from 23:38PM until 23:59PM; and on the 5/23 sheet, the same patient would be listed from 0:00 to 1:10. Perhaps you could add a column between Column F and Column G labeled "MDS" (for "Multi-Day Span"), or whatever makes sense to you, and simply add an "X" if the patient was in past midnight and should then also appear on the next day's data listing for that room.

This approach seems it would solve your problem.
 
Upvote 0
Is N calculated by formula or it's entered manually? If it is calculated, you change the formula so it enters 1 into the cell. 0:00 has dual personalities. It is 0 if it is beginning of a day and 1 if end of the day. In fact, any integer is displayed as 0:00 in time format.
 
Last edited:
Upvote 0
ErikTyler said:
Terry, it seems to me that if a patient is in the room past midnight, they'd simply need to be entered TWICE: once for date 1 and once for date 2. It's the only way I can see that you'll be able to keep ONE graph per ONE day.

So if a patient was in from 5/22 at 23:38 until 5:23 at 1:10, then for the 5/22 sheet, the patient would be listed from 23:38PM until 23:59PM; and on the 5/23 sheet, the same patient would be listed from 0:00 to 1:10. Perhaps you could add a column between Column F and Column G labeled "MDS" (for "Multi-Day Span"), or whatever makes sense to you, and simply add an "X" if the patient was in past midnight and should then also appear on the next day's data listing for that room.

This approach seems it would solve your problem.
Click to expand...



Well, I ended up just creating many calculated columns to account for this, rather than create duplicate accounts, (Although an excellent suggestion) I need to keep the data in a cube format for additional pivoting and report.

Thanks for your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
266
AOB
AOB
C
  • Question
Ordinary Hours or Penalty Shift
Replies
0
Views
74
ChetanPuri
C
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
310
awoohaw
awoohaw
N
  • Question
Need Formula help
Replies
5
Views
358
AhoyNC
A
M
  • Question
Complexed Time Difference Formula
2
Replies
17
Views
1K
mahmed1
M

Forum statistics

Threads
1,214,895
Messages
6,122,128
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top