Need function suggestion

[::CORY::]

I need some help in getting Excel to automatically give me some data from
another portion of the spreadsheet.

Scenario:

I have a schedule matrix made in Excel similar to this:

A B C D
1 Emp | 9am | 10am | 11am |
-------------------------------
2 Bob | | 1 | 1 |
-------------------------------
3 Tom | 1 | 1 | |
-------------------------------

For this example, I have cut the work day down to 9am to 11am, but it is
normally 9am to 11pm.
For each hour that the Employee works, a "1" is entered in for that hour so
it can be tallied for the day.
Each hour the Employee does not work is left blank.
What this example is showing is that Bob starts work at 10am and ends at
12pm (because he is here the full 11am hour) and it shows that Tom starts
work at 9am and leaves at 11am (because he is here the full 10am hour).

Now what I need to do is make Excel automatically figure out by looking at
this when the employee's shift starts and when it ends.

Right now, I am able to do this with nested IF statements:

Bob's Start Time: =IF(B2=1,"9am",IF(C2=1,"10am",IF(D2=1,"11am","etc")))

This works, however, since Excel will only allow 7 nested IF statements, I
cannot cover my entire day (9am to 11pm).

Does anyone have any suggestions as to how I may get it to figure out this
data ? I am sure there is probably an easier way that I am totally
overlooking, but I am at a loss.

Thanks,

::CORY::

 

[::Cory::]

Thanks Tom, that works great

--

::CORY::

Bobs Start Time =TEXT((MATCH(1,B22,0)-1)/24+TIMEVALUE("9:00"),"h AM/PM")

Bob End Time
=TEXT((MATCH(1,B22,0)-1+COUNTIF(B22,1))/24+TIMEVALUE("9:00"),"h AM/PM")

assume you want this at the end of the data and drag fill down the column.