On Apr 27, 2:52 pm, "T. Valko" wrote:
Well, I don't why you'd want to do that but you can try this:

=INDEX(B1:J1,MATCH(100,INDEX(B2:J5,MATCH(A10,A2:A5 ,0),)))+(INDEX(B1:J1,MATC*H(100,INDEX(B2:J5,MATCH( A10,A2:A5,0),)))=21/24)*30/1440

If the end time is 9:00 PM it'll add 30 minutes and return 9:30 PM

Biff

"Silver Rose" wrote in message

ups.com...



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

groups.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?- Hide quoted text -

- Show quoted text -

Thanks for your help Biff


The Formulas work well they now look like this

Start Time

=INDEX(StartFinishTimeRange,MATCH(0.5,INDEX(HoursI ndex,MATCH(A10,TeamMembers,
0),),0))

Finish Time

=INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursI ndex,MATCH(A14,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursI ndex,MATCH(A14,TeamMembers,
0),)))=21/24)*30/1440

I am still needing to refine them though how do I now get this finish
time formula to work for any finish time so I can just copy this
formula to all staff members and it will work regardless the finish
time?

Also when I have a staff member not working all cells are left blank I
need the above formulas not to return #N/A to the start finish time
cells but to instead return a result of text "OFF" or maybe it could
pass " ----" . This is needed because i pass the cells with the start
finish times to a roster summary page which is the roster the staff
read when printed. Formulas on the roster summary currently can handle
"OFF" being passed to it.

I have tried putting a ISNA formula into the formulas but it errors
out and excel won't allow me to save the formula. one of the errors it
may come up with is that the user has restricted the format of the
cell. which I have tried setting as hh:mm AM PM and also as general? I
got to admit I have no real idea what I am doing with the ISNA formula
as the current formula is getting pretty big.

Currently i have tried this with the finish formula haven't tried the
start formula yet some attempts are shown below

=IF(ISNA(INDEX(StartFinishTimeRange,MATCH(100,INDE X(HoursIndex,MATCH(A10,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursI ndex,MATCH(A10,TeamMembers,
0),)))=21/24)*30/1440,"",INDEX(StartFinishTimeRange,MATCH(100,INDEX (HoursIndex,MATCH(A10,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursI ndex,MATCH(A10,TeamMembers,
0),)))=21/24)*30/1440

it still returns #N/A

also have tried this

=IF(ISBLANK(C10:AE10),"",INDEX(StartFinishTimeRang e,MATCH(100,INDEX(HoursIndex,MATCH(A10,TeamMembers ,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursI ndex,MATCH(A14,TeamMembers,
0),)))=21/24)*30/1440)

still returns #N/A

Any ideas

Thanks

Silver Rose