Rotating Work Schedule

[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?

 

[wutzke]

sorry the cut and paste messed up the formating

 

[Herbert Seidenberg]

With monster formula:
http://www.freefilehosting.net/download/3ee29

 

[wutzke]

With monster formula:http://www.freefilehosting.net/download/3ee29

thanks
http://clip2net.com/clip/m6196/1207058189-clip-35kb.png

 

[wutzke]

With monster formula:http://www.freefilehosting.net/download/3ee29

thanks
but I'm getting an error

http://clip2net.com/clip/m6196/1207058189-clip-35kb.png

 

[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.

 

[Herbert Seidenberg]

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

 

[Herbert Seidenberg]

Alternate download site:
http://www.savefile.com/files/1555341