Find First Non blank cell than find column header and return that value
On Apr 26, 3:58 am, "T. Valko" wrote:
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.- Hide quoted text -
- Show quoted text -
Thankyou these work great. One problem I have with working with blocks
of time though is the finish time will return result of 9 pm however
it needs to say 9:30pm. I am thinking I need to somehow have the
finish time add 0.5 (half hour) to the result so it reads 9:30pm. Any
ideas?
|