Number of Counts between Time Ranges

Hey all,

I was looking for a formula that would count the number of patients in a waiting room as a fraction of the amount of time they have been waiting per 1 hour intervals. I have attached an excel sheet that will explain it better than I can.

Black indicates overlap of a 1 hour interval
Red indicates patient waited withina 1 hour interval with no overlap

I have provided extra hours at the end for a patient waiting past midnight as to be accounted for.

Thanks again, much appreciated!!!

SY