Formula- count # of guests for every hour based on arrival and end time

Hello,

I am trying to count the # of guests there were at every hour from arrival to end/departure time. All are night guests and most were there past midnight.

I used the formula suggested in one of the threads here but it's giving me negative results and/or not accurately counting guests.

Thanks in advance for your valuable help.

Jomejorada

Thanks, I copied your formula but it's resulting to 0.

Is it possible for you to send the attachment with your formula?

Thanks for your help.
J

Thank you very much for your help! You just made my day!!!

Hi again,

I'm sorry I just got the chance to review this closely, looks like it doesn't capture everything, e.g. 24:00-24:59 is 0 when there are guests at this time frame. Thanks again.

Hello Dilipandey,

Please see attached with my expected results. Thanks much for your help and time.

Jocelyn

Hello Dilipandey,

I think we still need to count them because they were there at the start of the hour (e.g. 24:00 for 0:18).

On the attached, I used a formula I found from another thread that was able to capture almost all except for the 23:00 and 24:00 (didn't capture all). If you could check what's missing on the formula why it's not capturing everything.

Thanks for all your help and patience.

J