Scheduling Workbook

[Corey]

Kind of an awkward situation really. My boss has asked me to set up a
worksheet that allows him to "color in" the cells corresponding to the
shifts that workers request/will work. I did so by giving him a
legend to copy from that has text in each item that matches its
designated color. In any case, what I'm hoping to accomplish, is to
some how translate these 'x' cells into an actual read out of the time
that they will work. In other words, if someone works 12 am - 8 am,
he would use place the "color" in those 8 cells, essentially giving
just those 8 cells a non-blank value. I want to find a way to show
this series of filled in cells in terms of the times those cells
correspond to. Sorry about the abstract phrasing of this, but it's
really hard to understand without seeing it.

Also as a side note, suppose someone works 2 different shifts in one
day, is there anyway to skip the gap in time and basically show both
different slots of time. This may be more than can be accomplished,
but any help/insight into this regard would be greatly appreciated.
Thanks so much!

 

[Gary''s Student]

This may be useful. Let's say that background colors have benn entered into
cells A1 thru A100. Green for 9:00 to 5:00; yellow for 5:00 to midnight; red
for midnight to 9:00. Enter this UDF:

Function shift(r As Range) As String
Application.Volatile
shift = ""
n = r.Interior.ColorIndex
If n = 10 Then shift = "9:00 - 5:00"
If n = 6 Then shift = "5:00 - midnight"
If n = 3 Then shift = "midnight to 9:00"
End Function

Then in B1 enter =shift(A1) and copy down

This will display the shifts as text