Rotating Work Schedule

W

wutzke

I have put
Monday Tuesday Wednesday Thursday Friday
Saturday Sunday
3/24/2008 3/25/2008 3/26/2008 3/27/2008 3/28/2008
3/29/2008 3/30/2008
8am - 5pm 8am - 5pm OFF OFF 11am - 8pm 11am - 8pm
10am - 6pm
in cells C60 thru I62

cell C60 contains =TEXT(C61, "dddd") to reurn the Day Name. This is
repeated thru I60

cells C62 thru I62 represent work shifts. Note Sunday's shift is
always 10-6

The next week the OFF day move forward 1 day so that

Monday Tuesday Wednesday Thursday Friday
Saturday Sunday
3/31/2008 4/01/2008 4/02/2008 4/03/2008 4/04/2008
4/05/2008 4/06/2008
10am - 7pm 8am - 5pm 8am - 5pm OFF OFF 11am -
8pm 11am - 8pm

Note the addition of 10-7 shift, thus 04/08/2008 becomes 10-7

To rotate this I used
=OFFSET(C62,1,-1)
in cells K62 thru Q62

except Monday has to reference differently, so
=OFFSET(C62,0,6)

To make sure that Sunday is always 10-6 until OFF, the formula changes
to

=IF(AND(TEXT(Q61, "dddd")="Sunday",
(OFFSET(I62,0,-1)<>"OFF"))=TRUE,"10am - 6pm",OFFSET(I62,0,-1))

this works for all cells Tuedays thru Sunday, Monday now must be
=IF(AND(TEXT(K61, "dddd")="Sunday",
(OFFSET(C62,0,6)<>"OFF"))=TRUE,"10am - 6pm",OFFSET(C62,0,6))

I can Copy and Paste this group of formulas across a new group of
cells representing weeks and the day off rotate continues.


NOW that being said...
I want to add another row representing a new series of shifts for
another worker

Monday Tuesday Wednesday Thursday Friday Saturday
Sunday
3/24/2008 3/25/2008 3/26/2008 3/27/2008 3/28/2008
3/29/2008 3/30/2008
8am - 5pm 8am - 5pm OFF OFF 11am - 8pm
11am - 8pm 10am - 6pm
[empty] [empty] [empty] [empty] [empty]
[empty] [empty]
11am - 8pm 10am - 7pm 8am - 5pm 8am - 5pm OFF
OFF 11am - 8pm
[empty] [empty] [empty] [empty] [empty]
[empty] [empty]

Now the direct reference to the Date cell (K61 thru Q61) won't work.
Do I create yet another offset reference or is there a difference way?
 
H

Herbert Seidenberg

Could not duplicate error.
Tried Excel 2002, 2003, 2007, Windows 2000, ME, XP.
Maybe you can verify that the names
in the formula are defined correctly by going to
Insert > Name > Define
Or troubleshoot the formula by substituting 1s or 0s
for different arguments, like
=if(AND(1,1),1,0)
until error disappears.
Tools > Formula Auditing > Evaluate Formula
gives #Name? in Excel 2003 but works for Excel 2007.
 
H

Herbert Seidenberg

Make sure that
Tools > Add-Ins > Analysis ToolPak
is checked.
That will enable Weeknum()
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top