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.

maybe this to find the first in row 24
=MATCH(0.05,24:24,0)

so the formula if times in row 21 would be
=INDEX(21:21,1,MATCH(0.05,24:24,0))

and this to find the last
=INDEX(21:21,1,MATCH(0.05,24:24))

--
Don Guillett
SalesAid Software

"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.

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.

P.S.

You'll have to format the formula cells as TIME

Biff

"T. Valko" wrote in message
...
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.

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?

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,MATCH(100,INDEX(B2:J5,MATCH(A 10,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

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?

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

On Apr 27, 9:32 pm, Silver Rose wrote:
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

oups.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,TeamMember*s,
0),),0))

Finish Time

=INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursI ndex,MATCH(A14,TeamMember*s,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursI ndex,MATCH(A14,TeamMember*s,
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,Te*amMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursI ndex,MATCH(A10,TeamMember*s,
0),)))=21/24)*30/1440,"",INDEX(StartFinishTimeRange,MATCH(100,INDEX (HoursIn*dex,MATCH(A10,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursI ndex,MATCH(A10,TeamMember*s,
0),)))=21/24)*30/1440

it still returns #N/A

also have tried this

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

still returns #N/A

Any ideas

Thanks

Silver Rose- Hide quoted text -

- Show quoted text -


Hi figured out previous post on #N/A

I Found and solved the problem had to use ISNA for Start Time and
ISERROR for Finish Time oh and must not forget ctrl + shift + enter.

Start Time

=IF(ISNA(INDEX(StartFinishTimeRange,MATCH(0.5,INDE X(HoursIndex,MATCH(A20,TeamMembers,
0),),
0))),"",INDEX(StartFinishTimeRange,MATCH(0.5,INDEX (HoursIndex,MATCH(A20,TeamMembers,
0),),0)))

Finish Time

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

However these formulas work too well for all staff who don't have
breaks its ok but staff who have Breaks are represented with blanks.
and thus above formula gives a blank start finish time result for
those staff who have breaks. So now my simple formula to add all 0.5
cells to equal actual hours worked will have to be changed because its
just to complex to change the above formula to work out breaks.

si fix one thing break another

so now i am thinking to add another column with tick boxes to tick if
a break is taken then to somehow write my hours worked formula to
check if box is ticked if so minus 0.5 from result any ideas?

Some criteria I have to work with is if age is <=17 than 1hr break
must be taken after 4hrs worked so if they are rostered 6hrs they get
paid 5, all other staff if they have worked 6 hours they are to take a
1/2hr break so if they are rostered 7.5 hours they get paid 7hrs. so I
would have to reference my worksheet which states their age.

Silver Rose