On the hour check between two time fields over 24 hours

[telobamipada]

I have a start date/time stamp in column A and a stop date/time stamp in column B and then in columns C thru Z I have 0:00, 01:00, 02:00 through 23:00 and hundreds of rows of data. I need to count in columns C:Z if thisperson was here during that hour... for instance, column C would check to seeif the time stamp in column A was between 12:00:00 AM and 12:59:59 AM and count if true,then column D would check to see if the time stamp in column B was between 1:00:00 AM and1:59:59 AM, and so on. I was attempting to use nested if statements and it doesn't accept the times I'm using inthe formula... basically an 'on the hour' check to see if they were present or not based on their start and stop times. It's for productivity, I hope this makes sense! Thanks in advance for any help! Unable to insert image by url above??

 

[telobamipada]

Let me rephrase this question… I have start times in column A and end times in column B. I need to know for each hour on the hour if that customer was here or not. So I have 24 columns C Thru Z for each hourly check with the heading 0:00, 01:00, 02:00, 03:00 and so on. If a customer came in at 10:56 and left at 14:10 I would see a count at 11:00, 12:00, 13:00 and 14:00 on that row. The next customer arrived at 12:15 and left at 17:15 and I would see a count at 13:00, 14:00, 15:00, 16:00 and 17:00 on that row… in the end, I will be able to see a summation of those columns and at which times we have the most customers present. If there is a better way, I’m all ears! Thanks so much for any help with this.

 

[WaterGypsy]

Have you tried :

=IF(AND($A2<=C$1,$B2>=C$1),1,0)

In C1,then copy across and down

 

[telobamipada]

Thank you! This works perfectly when the visit doesn't span midnight... could I use a date/time field and only look at the time in the formula? This way, even if it spans midnight it would pick it up?

 

[WaterGypsy]

You could try

=IF(OR(AND($A2>$B2,OR(C$1>=$A2,C$1<=$B2)),AND($A2<=$B2,$A2<=C$1,$B2>=C$1)),1,0)

 

[telobamipada]

You're a genius! You don't know how much I'd like to buy you a beer right now! Thanks so much WaterGypsy!

 

[WaterGypsy]

You're a genius! You don't know how much I'd like to buy you a beer right now! Thanks so much WaterGypsy!

Glad I could help