Help - populate a schedule using start/finish times?

[Will]

Hi,

I have a an excel spreadsheet which lists names and start/finish times per
day of various workers. Crude example:

A B C D E
................................... etc
1 Monday Tuesday
2 Linda 1400 1800 1300 2100 (in integer format)
3 Bob 1500 1900 700 1400
..
..
etc

I would like to know if it is possible to use this to produce a schedule on
a seperate sheet by hour of the day, something like this:

A B C D E F G
1
2 Monday
3
4 13/14 14/15 15/16 16/17 17/18 18/19 19/20
5
6 Linda Linda Linda Linda
7 Bob Bob Bob Bob

If anyone could tell me if this is possible and (if it is) point me in the
right direction of what functions to use (or if I have to use macros/visual
basic) it would be much appreciated. I have Office 2007 at home but would
like to be able to use it with 2003 (at work) if possible.

Thanks,

Will.

 

[Will]

Unfortunately what I saw in the input box didn't equal what I posted above so
my "examples" are broken but I hope you can get the idea of what I am trying
to do

Will.

 

[Pete_UK]

Just put times (integers) in row 4 - I put this in A4, B4 etc:

600, 700, 800 etc across to 2400 in S4.

Then you can put this in A5:

=IF(AND(Sheet1!$B2<=A$4,Sheet1!$C2>=B$4),Sheet1!$A2,"")

Copy this into A6, then copy A5:A6 across to R5.

I'm not sure where you want Tuesday's results to go - please feed
back.

Hope this helps.

Pete

 

[Will]

Thanks, that works! Tuesday will go on another sheet and I have worked out
how to do that from your help below, thanks again.

Will

 

[Herbert Seidenberg]

Excel 2007 Pivot Table.
Maybe not what you want,
but it looks neat:
http://www.mediafire.com/file/dnmd2t4ndmj/03_09_09.xlsx

 

[Pete_UK]

You're welcome, Will - thanks for feeding back.

Pete