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

You can reduce those formulas a little by using COUNT. No real need to use
ISNA. It just makes the formula twice as long.

=IF(COUNT(rng)<2,"Off",INDEX(..................... .......))

As far as accounting for breaks...

You could enter the length of the break in another cell and then subtract
that from the end time. Format those cell as TIME and enter 1:00 for a 1 hr
break and enter 0:30 for a 30 minute break. Then:

=IF(COUNT(rng)<2,"Off",INDEX(..................... .......)-break_cell)

Biff

"Silver Rose" wrote in message
oups.com...
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

Hi thankyou for everyone's input

Each of the suggested formula's for finish times and for checking for
blanks work well however only for fixed or static data. However my
hours index data is always changing and therefore the formula no
longer works I would have to edit every formula depending on the data
it was checking which is not my intention.


=IF(COUNT(rng)<2,"Off",INDEX(StartFinishTimeRange, MATCH(100,INDEX(HoursIndex,MATCH(A8,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursI ndex,MATCH(A8,TeamMembers,
0),)))=21/24)*30/1440)

I am seeking to amend the finish time formula so it adds the 0.5
(1/2hr) so it can work for any data entered eg. the hours index is
always changing and still give the correct result. How do I get it to
say any time (result) that is returned add 1/2hr? at present the
formula is fixed to 9pm



also this above formula returns OFF regardless if there is data or no
data in the hours index

and with the return "OFF" portion of the finish formula again we are
dealing with dynamic data not static data. I need that if all blanks
are present with no data than return result "OFF" at present this
portion of formula overides time result and give's all answers as
"OFF". The formula should only return "OFF" if there is no time
result to be returned. Also keeping in mind i don't want to get any
error result message


Thanks again
Silver Rose

Would it be possible for you to send a copy of the file to me so I can see
what you're trying to do? It sounds like you're calculating work hours but
it seems to me that you're doing it in a roundabout way.

If you can send me the file I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"Silver Rose" wrote in message
oups.com...


Hi thankyou for everyone's input

Each of the suggested formula's for finish times and for checking for
blanks work well however only for fixed or static data. However my
hours index data is always changing and therefore the formula no
longer works I would have to edit every formula depending on the data
it was checking which is not my intention.


=IF(COUNT(rng)<2,"Off",INDEX(StartFinishTimeRange, MATCH(100,INDEX(HoursIndex,MATCH(A8,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursI ndex,MATCH(A8,TeamMembers,
0),)))=21/24)*30/1440)

I am seeking to amend the finish time formula so it adds the 0.5
(1/2hr) so it can work for any data entered eg. the hours index is
always changing and still give the correct result. How do I get it to
say any time (result) that is returned add 1/2hr? at present the
formula is fixed to 9pm



also this above formula returns OFF regardless if there is data or no
data in the hours index

and with the return "OFF" portion of the finish formula again we are
dealing with dynamic data not static data. I need that if all blanks
are present with no data than return result "OFF" at present this
portion of formula overides time result and give's all answers as
"OFF". The formula should only return "OFF" if there is no time
result to be returned. Also keeping in mind i don't want to get any
error result message


Thanks again
Silver Rose