Calculate Total Hours each occurrence and Total Days for all occurences

Hello Forum,

I am a new member and hope to have your help with calculating time in hours and days.

Attached is my sample data (fictional information), which has several tabs.

Colour Legend
Data on worksheet from original source
Data to pull from Encounter Demographics worksheet to Case Log worksheet (based on "match" of "Encounter Number")
Derived from formula (added column not on worksheet from original source)

What I am trying to do:

During an encounter, a patient may be transferred between different units. Some units are designated "special care" - see tab "Unit List".

An ICU/Stepdown occurrence begins when patient first arrives in a special care unit and ends when the "Unit Nxt Tsf" is not a special care unit or patient is discharged (see exception for OR's).

The OR's are not special care units but if patient was in a special care unit BEFORE going to the OR the special care occurrence time continues until patient is transferred to another unit after the OR (may or may not be "special care") or patient is discharged.

Example 1 - patient in ICU goes to OR, returns to ICU from the OR, is transferred to Stepdown unit, then discharged directly from Stepdown unit. This would constitute one occurrence with the start time being first arrival in ICU and end time being discharge time.
Example 2 - patient on floor (not a special care unit) goes to ICU, is transferred to Stepdown unit, is transferred to floor, then discharged. Occurrence would start with time patient arrived at ICU from the floor, continue through time in Stepdown, and end when patient transferred to floor again.

Patients can have multiple ICU/Stepdown occurrences and multiple surgeries during an encounter, and each ICU/Stepdown occurrence can involve transfers between different "special care units" and the OR.

Need a formula that calculates the number of hours of each continuous ICU/Stepdown occurrence, listing them chronologically (up to 7 occurrences), and also calculate the total number of days in ICU/Stepdown, with the total days being shown on the case log.

I know it's the holiday season, but if anyone can help me solve this before Monday, I would very much appreciate it.

Thanks,

Hi FlameRetired and thanks for taking a look at this so quickly.

I've tried entering the formula you suggested in column P (on the Encounter Demographics worksheet), but it doesn't indicate "End ICU Occurrence" at any time for the first encounter number. For the encounter, the patient's final transfer was to the stepdown unit - where they stayed until the Discharge Date (as there were no further transfers listed). When this happens, I was thinking the formula would return "End ICU Occurrence" rather than "Continued".

The descriptive part about the colour coding is just there as a reference - I didn't know if it mattered where the information originated - so I gave as much information as I could.

If there is anything else you would like clarified, please let me know; and thank you again for your help with this. Excel is wonderful, but I'm still learning the more advanced formulas and functions, and very glad to have help.

Hi,

I copied the new formula into column P on the Encounters Demographics sheet, which now shows "End ICU Occurrence" in cell M5 as it should. But, now the second and third encounters both show as "Continued" rather than showing "End ICU Occurrence" as they did with the previous formula. Do I need to combine the old and new formulas?

Next, once I have the "End ICU Occurrence" in column P, can a formula in column Q somehow calculate the ICU minutes (i.e. if column P has End ICU Occurrence, use the occurrence end date time from column K and subtract the occurrence start date time from column J - going up to the row where that occurrence began?

I'm not sure I'm explaining it clearly, but thrilled that you're helping me with this.

That works. I think there is only one other scenario to address in that column, which is when a patient has more than one ICU/Stepdown occurrence - and I just realized my sample data doesn't include that. For example, if the patient was transferred to the floor (A103) and stayed there for a week, THEN went back to ICU for 3 days, I would need two separate ICU/Stepdown occurrences within that one "encounter", so there would be two rows with "End ICU Occurrence" for the same patient within the same encounter. And I need the total minutes/days for each occurrence, and a total days for the encounter (units have to be whole numbers and rounded up so any part of 24 hours counts as 1 day).

Would it help if I create a larger sample data - say 50 patients?

Let me know.. and thank you so much for your time on this.

Okay. I have to head out for an hour or two, but will upload larger sample data later tonight. I will include the explanations of logic as requested.

I'm back but I keep getting logged out when I try to post...

But it worked that time, so ... I'm almost done with the larger sample data that includes every scenario I can think of. What is the limit on file size to upload here?

Hi Flame Retired, I'm going to upload what I have. I've changed the column headings to include extra info (i.e., desired result, etc). Where I indicate minutes/days results, this is just by point and click for each, so need a formula that works for all. Red/Green indicate where current formula not giving desired result. Here goes...

Message says the larger sample file exceeds the limit of 1,000 (file is 1,200)... can I upload a zip file?

Hi... now the file is small enough to upload... hope this helps.

IT WORKED !!! Wow, I don't know what to say! This is wonderful. Let me know if there is anything else I can do to help with the minutes/days part. Did you have a chance to see if my attempt was on the right track? I'm sure whatever you come up with will be great and I look forward to hearing back from you.

For the minutes in column R, I was looking for it to be rounded up to the next minute if any decimal (or it can be rounded to the next whole hour) for each "End ICU Occurrence". Then, for the days, rounded up so 1.3 days would have to show as 2 days. And for the total ICU days for the whole encounter, need the total days of the ICU occurrences. Example - if one occurrence is 2 days and second occurrence is 3 days, then total days for that encounter would be 5 days.

Does that help?

FYI: ZIP file max is 9.77 MB.

Actually, you have me wondering about it now. Can you do a column for each way (just in case I'm wrong and won't be able to confirm it until next week).

As many helper columns as you need is okay with me!

On the larger sample data I deleted the Case Log worksheet that was on the original sample. Once all the time elements are working, I have a column on the Case Log (every encounter has only one line) that I need to have a formula that pulls the Total ICU Days for each encounter. I think I can figure that part out with index/match but might need help.

The formula you came up with for triggering "End ICU Occurrence" makes total sense when I look at the individual parts. By checking that there is a number/date in each of the discharge/time in/time out columns and that they are are filled (total 3), makes sure the patients with no ICU time or not yet discharged have a "blank" in the column. Then the countifs make sure that the patients with only one line of ICU time come up as "End ICU time". And using the lookup with Yes/No works for all the others. I might not be wording that correctly, but I think I understand how the parts of the formula come together to make it all work. It's fantastic!

Thanks JohnTopley! I appreciate the info on file size limits.

Hi Joris,
Thank you for having a look at this and the proposed solution you have offered.

My concern is that the formula you use in the minutes column contains references both column P and column Q. Column Q is only there (temporarily) to manually indicate what I wanted the formula in column Q to calculate, but it would be deleted from the worksheet, leaving only column P as a reference for other formulae.

I tried to switch your formula references from Q to P, but then it doesn't work. Any suggestions how to eliminate any reference to column Q and use column P as the reference?

I appreciate your efforts.

Thanks

Hi Joris,

From what I've read (and tried) with array formulas, I know they can be powerful, but cause too many problems if the users (including me) are not well aware of them. Also, from what I've tried, the data can't be sorted or rows added/deleted with arrays, and I need to add new lines every 2-3 days.

Is there a way to make the formula work without the array (no Ctrl-Shift-Enter)?

Thanks
PS I agree Excel is fun to use and try new things with

Hi all,

I finally managed to solve the issue with helper columns and a formula that didn't need to be an array! Thanks so much for the help along the way.

Hi FlameRetired,

I may actually have spoken to soon when I said it was solved; ran into a glitch today - will post if I get it fixed. Hope your situation worked out okay. If you have any thoughts on a solution to my time calculation question, I would still be interested in your thoughts.

Thanks,

Hi FlameRetired,
For the sample encounter 90736490039 you mentioned, there would be a time gap between the time of transfer to the OR and the return to ICU, but the bed is still assigned so the OR gap is considered part of that ICU occurrence (hence calculated cell = Continued). The first ICU occurrence would end on the next line when transferred to floor (non-special care unit).
There would again be a time gap from transfer to the floor until return to ICU for a second occurrence (and this gap must be excluded from time calculation for special care). Therefore, second ICU occurrence would begin on at the "time in" on the next line and continue until the next "end ICU occurrence.
Dealing with the non-ICU time gaps seems to be the issue I can't overcome. Maybe another helper column is needed that would "look up" the "unit" (in addition to the "next tsf unit") - to determine the start time of the second, third, fourth ICU occurrence(s).
Does that make sense?
The array formula joris offered seems to work for everything except the ones that have multiple separate ICU occurrences that need separate time calculations.
Thanks for having another look at this.

Hi FlameRetired,

J10 - patient arrives, so this is start time of 1st ICU occurrence.
K10 - patient either went to the OR or transferred to another ICU bed, so 1st ICU occurrence continues. In this case, you see a time gap between K10 and J11 (so patient was in OR but bed in ICU was still his/hers, so 1st ICU occurrence continues over the gap period.
K11 - patient transfers to another ICU/stepdown unit, so no time gap between K11 and J12 - 1st ICU occurrence continues.
K12 - patient transfers to a NON-ICU room, so 1st ICU occurrence ends - to recap - start time would be J10 and end time would be K12 for this 1st occurrence.
Time gap between K12 and J13 is when the patient was in a NON-ICU room (reported and funded differently, so cannot be included in ICU time calculation). --> maybe the key is to determine the gap minutes and subtract it??
J13 is when patient returned to ICU for a 2nd Occurrence - start time would be K13 and end time would be K14 for this 2nd occurrence.
Does that help?

Hi Joris,
I'll have a look and test it to see if everything works now. Although, I really don't understand array formulas - whatever works is what I need.
Thanks for your continued interest and assistance.

Hi FlameRetired,
I'll test it out at work tomorrow to see if it works, but now also need to add another criteria. So far, what I've been attempting to automate is the calculation of total ICU time by occurrence and total for encounter. Now, I also need to know how much of that ICU time was Post-operative. For this, I would need to have this worksheet look up the encounter number and pull the OR stop time, then compare that to the ICU start times to determine which part of the total days relates to the post-op period. Is this possible?
Look forward to your insights.

Hi FlameRetired,
I'll test it out at work tomorrow to see if it works, but now also need to add another criteria. So far, what I've been attempting to automate is the calculation of total ICU time by occurrence and total for encounter. Now, I also need to know how much of that ICU time was Post-operative. For this, I would need to have this worksheet look up the encounter number and pull the OR stop time, then compare that to the ICU start times to determine which part of the total days relates to the post-op period. Is this possible?
Look forward to your insights.