staff monthly scheduling problem

[cutemom2jen]

I have recently been given an added responsibility at work that require
me to prepare a schedule for a staff of 10, some part time, some full.
I have been racking my brain for a month and trying every trick o
formula I could to develop a monthly calendar with set shifts (5/day
for unchanging employees that would calculate hours for each employe
by week and by month. I would like to be able to program something fo
example:

Gary works Tues-Sat 6:30AM-3:00PM, with a 30 minute lunch. The lunc
period does not need to be shown on the calendar since this woul
ideally be a hidden formula only necessary for calculation but no
required to be visible on the staff schedule I post. Janie work
Sunday through Thursday 11AM-7:30PM....... and so on.

Can someone give me some direction pleeease? see example of what wan
visually, please note the absence of hour totals

Attachment filename: september04gh.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=69101

 

[Sandy Mann]

It may be that you have received no replies because very few people will
open an attachment for fear of viruses or other nasties and you have not
given very much detail in the narrative.

I assume that you just want formulas to calculate the hours and not assign
the shifts and that all times are entered as Excel times. You also talk
about a calendar, a calendar is easy for humans to understand but it is not
a good layout for Excel because the data is fragmented all over the sheet.
You understand that the cells contained within a border are all referring to
one day but Excel just sees a group of cells that looks no different to the
rest of the cells.

That said, I constructed a shift calendar as follows. It is a bit of a
cludgie workaround - perhaps one of the regulars will post a more elegant
solution. In constructing the formulas walnuts and sledgehammers kept coming
to mind.

In Column A Rows 4 - 13 I listed staff names, Row 3 Columns B, C, D & E were
headed as "In", "Out", "In" "Out" This constructed the first "Day" and was
repeated across to Column AC and down to Row 62 to give sufficient "days" to
accommodate the calendar.

You seemed to unconcerned about meal breaks but if they are to be taken into
consideration then they must be accounted for in the sheet especially if
staff are going to work rotating shifts etc. That is why I elected to have
two In's and two Out's each day. If there is no meal break then simply make
both middle In and Out the same time. If you don't want the meal breaks to
show simply hide the columns.

In cell AD4 I entered the formula =A4 to give the 1st staff name and in AE4
the formula
=SUM(C4,E4,G4,I4,K4,M4,O4,Q4,S4,U4,W4,Y4,AA4,AC4)-SUM(B4,D4,F4,H4,J4,L4,N4,P
4,R4,T4,V4,X4,Z4,AB4) and format the cell as "[h]:mm" (without the quotes -
the square bracket stop the hours from rolling over into days when they
reach 24). If there is any chance of shifts going over midnight then you
will have to used the formula
=SUM(MOD(C4-B4,1),MOD(E4-D4,1),MOD(G4-F4,1),MOD(I4-H4,1),MOD(K4-J4,1),MOD(M4
-L4,1),MOD(O4-N4,1),MOD(Q4-P4,1),MOD(S4-R4,1),MOD(U4-T4,1),MOD(W4-V4,1),MOD(
Y4-X4,1),MOD(AA4-Z4,1),MOD(AC4-AB4,1)) instead. Copy both cells down and
this will give a list of the hours that each individual staff member has
worked each week.

Now, in AD67:AD76 I entered the staff names and in AE67 the formula
=SUMIF(AD4:AD62,AD67,AE4:AE62) and copied that down and again formatted it
as "[h]:mm". This gave the total hours for each staff member for the month.
As you can see a calendar layout is not an easy layout to apply formulas to.
There is much more information on time formulas in the following (in no
particular order):

http://www.cpearson.com/excel/overtime.htm
http://www.j-walk.com/ss/excel/files/index.htm
www.cpearson.com/excel/datetime.htm
http://www.cpearson.com/excel/DateTimeWS.htm
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://j-walk.com/ss/excel/tips/top13.htm

If you want me to send a sample spreadsheet direct then e-mail me your
address.

HTH

Sandy

 

[cutemom2jen]

A separate sheet would be fine. Hidden columns will be fine. I jus
need the worksheet or workbook to print a calendar with the basi
information shown on my sample spreadsheet for posting. I apologiz
for my inexperience with proper posting techniques. My email address
if you like, is (e-mail address removed).
Thanks for your help.
Tiffan

 

[cutemom2jen]

Sandy

I couldn't locate your user info but I am clearly inexperienced. Than
you for your help and my email address is (e-mail address removed).
Tiffan

 

[cutemom2jen]

Sandy

I couldn't locate your user info but I am clearly inexperienced. Than
you for your help and my email address is (e-mail address removed).
Tiffan