Automatically enter data based or start and finish times

[PaulEnglish]

Im trying to automatically enter values based on start and finish time
of staff shifts.

In column B I have staff names and in column g I have the shift star
times and in column h I have shift end times. From column i I have al
24 hours, from 00:00, in half hour intervals. If the staff membe
starts at 08:00 and finishes at 16:00, I want to enter a value of 1 i
the column with 08:00 in it and continue to do so until the column wit
16:00 in it. Then at 16:30 column, I want a zero.

I have tried this formula

=IF((AND($G5>I$3,$H5>I$3)),1,0), which only works for some of th
shifts.

Any ideas on what I can do?

Thanks:mad

 

[jeff]

Hi,

I think this is what you want if I have my columns
straight:

=IF((AND($G5<=I$3,$H5>=I$3)),1,0)


Jeff

 

[jeff]

....I've thought about it..

this might be even better - it puts a '1' in the first
time slot (ie 8:07 AM will now show a '1' in the 8:00AM
slot, whereas the other would show starting in 8:15 AM)

=IF((AND($G5<=H$3,$H5>=H$3)),1,IF(AND
($G5>=H$3,$H5<=I$3),1,0))

jeff

 

[PaulEnglish]

Thanks I finally worked it out, I needed to precedent the formula on th
row 3

=IF((AND(I$3>=$G5,I$4<=$H5)),1,0)



Thanks for the help