Another approach...

Time headers in B1:J1
Staff names in A2:A5

A10 = some staff name to lookup

Start time:

=INDEX(B1:J1,MATCH(0.5,INDEX(B2:J5,MATCH(A10,A2:A5 ,0),),0))

End time:

=INDEX(B1:J1,MATCH(100,INDEX(B2:J5,MATCH(A10,A2:A5 ,0),)))

Biff

"Silver Rose" wrote in message
oups.com...
Hi I have a excel spreadsheet whith columns which represent time and
rows that represent staff and each row has multiple columns (cells)
which represent 0.5 or 1/2 hour blocks of time which are also colour
filled for easy visual reading eg.

Time 7:30 8:00 8:30 9:00 9:30 10:00 10:30 11:00
11:30 12:00 12:30 1:00 1:30 etc
Staff
Joe 0.5 0.5 0.5 0.5 0.5
0.5 0.5
Mary 0.5
0.5 0.5 0.5 0.5 0.5 0.5

This equates to Joe starting his shift at 8:00am and finishing at
11:30am. How do I do a formula which looks up the first occurance of
0.5 of a given staff member to give a starting time and the last
occurance of 0.5 to give a finishing time. There are multiple rows
which contain multiple staff. I have to keep the 0.5 increments to
calculate hours worked for day also helps with allowing meal breaks by
simply leaving a blank cell.