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

To handle these times it's easiest if you remember that Excel stores times as fractions of a day, so that 6am is stored as 0.25. However, 1.25 also represents 6am but on the next day, though if you format it as a time it'll still display as 6am (or 6:00 or whatever). If you type a few times in successive cells across as in your worksheet: 16:00, 17:00, etc. and then drag to fill to the right till you get past midnight, the values will look like 0:00, 1:00, 2:00, etc., but if you temorarily change the format from Date to General, you'll see that the times from midnight on are actually 1, 1.04167, 1.08333, etc. - that is, they're times on the next day. Which is exactly what you want in your situation, since subtracting 10:00 from 2:00 will give you a positive value. So I recommend you set up the times across the top of your worksheet like that.

But there's still the problem where your IN and OUT times are entered as, say, 10:00 and 2:00. I suggest that to handle this you modify your formula by incorporating a test of whether the OUT time is (or appears to be) less than the IN time. When it is, you just add 1 to the OUT time before doing the MEDIAN calculation. So your formula =IF(E$1=MEDIAN(E1,$C2:$D2),1,0) would become =IF(E$1=MEDIAN(E1, $C2,IF($D2<$C2, 1+$D2,$D2)),1,0). If you fill this across to the right, it should now work OK.

Hope that gives you the general idea.

Regards

Howard

On Sunday, 8 June 2014 11:40:00 UTC+10, wrote:
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