Excel - Find First Non blank cell than find column header and return that value

Asked By Silver Rose on 25-Apr-07 12:02 PM
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.




Don Guillett replied on 25-Apr-07 12:45 PM
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
dguillett1@austin.rr.com
T. Valko replied on 25-Apr-07 01:58 PM
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
T. Valko replied on 25-Apr-07 05:31 PM
P.S.

You'll have to format the formula cells as TIME

Biff
Silver Rose replied on 26-Apr-07 10:41 PM
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?
T. Valko replied on 27-Apr-07 12:52 AM
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(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 replied on 27-Apr-07 07:32 AM
MATC=ADH(100,INDEX(B2:J5,MATCH(A10,A2:A5,0),)))=3D21/24)*30/1440
by

Thanks for your help Biff


The Formulas work well they now look like this

Start Time

=3DINDEX(StartFinishTimeRange,MATCH(0.5,INDEX(HoursIndex,MATCH(A10,TeamMemb=
ers,
0),),0))

Finish Time

=3DINDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A14,TeamMemb=
ers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A14,TeamMember=
s,
0),)))=3D21/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

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

=3DIF(ISNA(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A10,=
TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A10,TeamMember=
s,
0),)))=3D21/24)*30/1440,"",INDEX(StartFinishTimeRange,MATCH(100,INDEX(Hours=
Index,MATCH(A10,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A10,TeamMember=
s,
0),)))=3D21/24)*30/1440

it still returns #N/A

also have tried this

=3DIF(ISBLANK(C10:AE10),"",INDEX(StartFinishTimeRange,MATCH(100,INDEX(Hours=
Index,MATCH(A10,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A14,TeamMember=
s,
0),)))=3D21/24)*30/1440)

still returns #N/A

Any ideas

Thanks

Silver Rose
Silver Rose replied on 27-Apr-07 10:21 AM
1,MATC=AD=ADH(100,INDEX(B2:J5,MATCH(A10,A2:A5,0),)))=3D21/24)*30/1440
nd
our
of
s by
mber=ADs,
mber=ADs,
er=ADs,
0,Te=ADamMembers,
er=ADs,
rsIn=ADdex,MATCH(A10,TeamMembers,
er=ADs,
rsIn=ADdex,MATCH(A10,TeamMembers,
er=ADs,


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

=3DIF(ISNA(INDEX(StartFinishTimeRange,MATCH(0.5,INDEX(HoursIndex,MATCH(A20,=
TeamMembers,
0),),
0))),"",INDEX(StartFinishTimeRange,MATCH(0.5,INDEX(HoursIndex,MATCH(A20,Tea=
mMembers,
0),),0)))

Finish Time

=3DIF(ISERROR(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A=
9,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A9,TeamMembers,
0),)))=3D21/24)*30/1440),"",INDEX(StartFinishTimeRange,MATCH(100,INDEX(Hour=
sIndex,MATCH(A9,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A9,TeamMembers,
0),)))=3D21/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 <=3D17 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
T. Valko replied on 27-Apr-07 02:32 PM
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



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,INDEX(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,INDEX(HoursIndex,MATCH(A9,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A9,TeamMembers,
0),)))=21/24)*30/1440),"",INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,MATCH(A9,TeamMembers,
0),)))+
(INDEX(StartFinishTimeRange,MATCH(100,INDEX(HoursIndex,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
Silver Rose replied on 28-Apr-07 01:25 AM
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(HoursIndex,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
result to be returned. Also keeping in mind i don't want to get any
error result message


Thanks again
Silver Rose
T. Valko replied on 30-Apr-07 12:56 PM
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