Formula to count number of patients per time interval over 24 hours+

Hi all,

I'm new to this forum so hopefully following all the rules!

I have been trying to work out a formula for capturing the number of patients in the hospital at half hour time intervals. There are a lot of formulas for capturing this information within a 24 hour period however not a lot of information when the Length of Stay or episode time is +24 hours.

As you can see in my example spreadsheet below, some of the patients stay for 244 hours (row 9).

The outcome that I am looking for is that a 1 is placed in all of the time slots when the patient is there. For example if they arrive on Jan 1st at 2.15 and leave on Jan 3rd at 10.30 all of the time slots in between would have a 1 placed in them.

I have been playing around a lot and think it is probably only possible if you set it up as I have in the example i.e. by having the date running down and the time running across.
Does anyone have any ideas about how this could work? I have tried SUMIFs and SUMPRODUCT formulas which generally work for Jan 1st but then go wrong for any date after that.

Thanks for your help!

Hi Tac,

Thanks for your response. It is helpful but do you think you could talk me through the logic of the formula a bit?

Thanks again!