Help with a schedule

  • Thread starter Thread starter Gadgetman
  • Start date Start date
G

Gadgetman

I need some help with a work schedule done in Excel.

The schedule is not very complex; however, when multiple changes to the
schedule are made for vacations ect, it becomes a garbled mess & I find
that some problems arise for me to be able to mentally determine how
many people are working during a given time when it's in text format. I
think it would be much easier to see in a chart if I was able to convert
the text into one. (A page of our current schedule can be downloaded for
reference at
http://homepage.mac.com/WebObjects/FileSharing.woa/wa/schedule.jpg.jpg-bi
nhex.hqx?a=downloadFile&user=gadgetman&path=.Pictures/schedule.jpg)

If there's a way to do it, I'd like to be able to chart a 24hr period
(6am one day to 6am the next day for example) and be able to see who's
working during that day with the start of the bars of the chart showing
the start time of the person's shift and the end of the bars of the
chart showing the stop time of the shift. I don't know if that makes
sense. I've tried to give an example of what I mean here:
http://homepage.mac.com/WebObjects/FileSharing.woa/wa/24hr_Chart.jpg.jpg-
binhex.hqx?a=downloadFile&user=gadgetman&path=.Pictures/24hr%20Chart.jpg

Any and all help would be appreciated! Thanks!

PS: if the above links for the sample files dont work, the sample files
can be seen/downloaded here:
http://homepage.mac.com/gadgetman/Schedule/FileSharing9.html
 
As it stands, I'm not sure if/how it can be done. But here's one crack at
it using a formulas approach (with a simple conditional format) to "plot"
the desired "chart" on a sheet. And the results are exactly the way you want
it to appear ! <g>

Here's the link to the sample file (full details inside):
http://www.savefile.com/files/5753695
File: Plotting_Schedule_Gadgetman_gen.xls

Steps (Easier to follow with the sample file):

In sheet: Plot
-------------------
Set up the lookup values in B1:Z1 (6a, 7a, 8a, ... 5a, 6aa)
Note that the only exception is the last value used in Z1: 6aa
which needs to be different to distinguish it from the 1st value in B1: 6a

Put in B2:Z2, the numbers: 25, 24 .... 1

B1:Z2 will serve as the HLOOKUP table for the criteria columns in Roster's
cols X and Y. (If desired, mask B1:Z2 by formatting the font color the same
as the fill color)

Put in A5:
=IF(MOD(ROWS($A$1:A1),2)=0,"",OFFSET(Roster!$B$3,INT((ROWS($A$1:A1)-1)/2),))
Copy A5 down to A103

(In A5:A103 will be auto-extracted the 50 consecutive names listed in
Roster's B3:B52. Names will appear in alternating rows. 50 is enough, I
hope!)

Put in B5, and array-enter the formula,
i.e. press CTRL+SHIFT+ENTER:
=IF(ISNUMBER(MATCH(1,(Roster!$B$3:$B$52=$A5)*(Roster!$R$3:$R$52>=B$2)*(Roste
r!$S$3:$S$52<=B$2),0)),1,"")
Copy B5 across to Z5, fill down to Z103

B5:Z103 will return 1's for the cells evaluating to TRUE, blanks: "" if
FALSE. The 1's returned will indicate the desired duty hours for each staff.
We'll then apply a simple conditional format to fill the cells with 1's with
black fill color (which also masks the 1's)

Select B5:Z103, apply conditional formatting
Under condition 1: Cell value is | equal to | 1
Format > patterns tab > Black > OK
Click OK at the main dialog

In sheet: Roster
----------------------
Put in R2: =Plot!A3

Put in R3, and array-enter the formula,
i.e. press CTRL+SHIFT+ENTER:
=HLOOKUP(INDEX(OFFSET($B$3:$B$52,,MATCH($R$2,$C$2:$O$2,0),),ROWS($A$1:A1)),P
lot!$B$1:$Z$2,2,0)

Put in S3, and array-enter the formula,
i.e. press CTRL+SHIFT+ENTER:
=HLOOKUP(INDEX(OFFSET($B$3:$B$52,,MATCH($R$2,$C$2:$O$2,0)+1,),ROWS($A$1:A1))
,Plot!$B$1:$Z$2,2,0)

Select R3:S3, fill down to S52

Depending on the date of interest input in Plot's A3, R3:S52 will reference
the correct columns for the date and convert the indications: 6a, 7a, ...
6aa in the columns to the numerics: 25, 24, ... 1, according to the hlookup
reference table set up in Plot's B1:Z2. The numbers: 25, 24, ... 1 are then
read by the formulas in Plot's B5:Z103

Note: Enter the indication as: 6a if the duty starts in the "From" col,
enter it as: 6aa if the duty ends in the "To" col

The set-up in Roster is for a week's worth (7 days)
Update/change the dates in C2, E2, … O2 accordingly
----

And when the set-up's complete: Just input/change the date of interest in
cell A3 in Plot (from amongst the 7 days listed in sheet: Roster). The
schedule bars will be auto-updated. Try it and see (there's already some
sample data loaded into Roster in the sample file)

--
 
Max,
Thank you for taking the time to try to help me! I'll give it a shot
and see how it works! I really appreciate it!
 
Back
Top