Scheduling

[Matt]

I am designing a schedule from a roster. I have start time, end time and
duration of shift.
I would like a formula to recognise the start time and end time and allocate
the value of 1 or fraction of one hour to a cell(s) so that I can visualize
coverage over the shift.
I have set up a sheet with Name Start time end time and duration of shift.
adjacent to this I have time periods in AM and PM, e.g 5-6, 6-7, 7-8,I want
a formula to recognize and the start time (or part their of ) and give a
value of 1 against the name of the staff member, if the staff doesn't start
until 10am then the cell has a nill value.
This is driving me nuts, any help would be much appreciated.

 

[JulieD]

Hi Matt

one way to achive what you want

...............A...............B...............C................D................E..............F
1........Name........Start..........Finish.............06:00.........07:00..........08:00
2.......Fred..........08:00.........16:00.............=IF(AND(D$1>=$B2,D$1<=$C2),1,"")

you can then fill the formula in D2 across and down
notice that i've not got ranges in D1, E1, F1 ... only the start of the
hour -

with the times you can have any format you like as long as excel recognises
them as time not text

Hope this helps
Cheers
JulieD

 

[Fred]

Matt -
I have a spreadsheet that creates a schedule and uses colors across the
workday to visually represent each employees shift. Send me an email
to: (e-mail address removed) and I will forward the file, if you're
interested.
- Fred