Hey,

We're trying to create a spreadsheet that will tell us how many people we have working each hour or half hour so we can compare that to our attendance during that time period. The other users are not terribly Excel competent so it's important that on the front end it be very simple to use.

Right now what I'm trying is a page where one puts in the employee and their in and out times. Then beyond that I have a table of times with a conditional that is supposed to determine if the hour in question falls between the in and out times of the employee. It puts a 1 if the employee was working then or a 0 if they were not. Crude diagram below:

Employee | Position | IN | OUT | 16:00 | 17:00 | 18:00 |
John Usher 17:00 19:00 0 1 1

My formula in E2-G2 etc. is =IF(E$1=MEDIAN(E1,$C2:$D2),1,0)

It seems to be working, but there's a serious limitation. We're a movie theatre, so most of our shifts go past midnight. If I put 2:00 in the out time (meaning 2AM the next day), it assumes that it's the same day as the in time when it's technically the next day.

Is there a more elegant way to accomplish what we're doing, or does anyone know a fix for the past midnight problem?

Thanks,
Stevens.judo