Retrieving Start and Finish Time for Different Days

Hello All,

I am having to create a rota system for where I work. This in itself is fine, the important people want a visual view of each team ad their individual shifts. However the also want an overview that they can look at each day with just the start and finish time viewable.

ie: -
Names - Start Finish
Bill - 08:00 11:00
Dave - 08:00 13:00

and so on. They then want to be able to select a different day from a drop down list and it provide the same information. I think it can be done using match/index possibly and I have used this for other things in the past but whether it is because it's 16:30 in the afternoon and my brain is fried I don't seem to be able to get my head around it.

I have included an example and any help or suggestion would be great.
The shifts are in different colours for a reason, although this won't affect the start/finish time. But I had to find a way of differentiating between them automatically. Again, any better suggestions are welcome.

I do have another related question. in the next uploaded example, I need to be able to specify when certain people maybe doing certain tasks within their day.

Ideally I would like to be able to have it look like the below example. But I'll happily accept any help that anyone could provide.

eg:-
Day 1
Help Phone
Start Finish Start Finish Start Finish
Dave 08:00 13:00 08:00 10:00 12:00 13:00

Mcmahobt kindly sorted the original enquiry and I've used his file as a basis, but I wondered if it was possible to the above with similar formulae.

Thanks in advance. Although I don't post much, I have used information in other threads a lot.

Previously Mcmahobt was kind enough to supply the formula below.

=INDEX(INDIRECT("'"&$K$3&"'!$B$1:$G$1"),SMALL(IF(INDIRECT("'"&$K$3&"'!$B2:$G2")<>"",COLUMN(INDIRECT("'"&$K$3&"'!$B2:$G2"))-1),1))

It enable me to find the earliest start time in a range. It was awesome, it did nearly everything I had asked for. However I now need to adapt it slightly and I don't know how. I now need to find the earliest start time in a range if a certain letter is in the range. I need it to find the earliest time that the letters "H", "M" or "P" are there. for instance someone may start at 8:00, but they are down to go to M at 10:00 and I need to show that on the rota. No one is down to work at more then one of these three letters at any point in the day, so they won't be at H and the M later.

How do I adapt this formula to show what I am after? There is a download above which is slightly different, it is Mcmahobt's example from above which I have adapted to to show what I need.

Any help would be greatly received as ever.

Thank you so much for replying and helping.

This is almost perfect, but I need the main Start Time & Finish Time (B Column & C Column) to show the overall time of working.

eg: on Day 1 Dave working 08:00 until 13:00 not 10:00 to 11:00 as it shows in the example. Then the Start and Finish Time under Help and Phone are perfect the way that they are.

I/we are so close.

This is amazing. Thank you ever so much. Seriously I appreciate everything you've done (as well as the others above). if you ever make it to Somerset in England I'll buy you all a drink, but for the moment you'll have to settle for added Reputation.

One question, with the finishing times column for the receptions is there an ability to choose the next column? So where it states someone finishes at 12:45, ideally they actually finish at 13:00 for when the next person starts. I'm not sure if it can be done, as it may be a limitation of the way I've set the initial week day sheets up.

Certainly.

I've created a tab called "What I'd Like". It is a copy of the Overview Tab. If you scroll down and look at the part that you've done for me and compare the two, you'll see that the times are all 15 mins later. Instead of 16:45 finish time it now says 17:00 for example.

What I meant to ask is where the formula you added finds the time, is it able to choose the next one along?

That works, it does give me an #value error for any empty cells, but I can get around that.

Again thank you. I shall consider this solved.