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?

Rotating Work Schedule - wutzke

sorry the cut and paste messed up the formating

Rotating Work Schedule - Herbert Seidenberg

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

Rotating Work Schedule - wutzke

On Mar 31, 10:39=A0am, Herbert Seidenberg <herbds7-ms...@yahoo.com>

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

Rotating Work Schedule - wutzke

On Mar 31, 10:39=A0am, Herbert Seidenberg <herbds7-ms...@yahoo.com>


thanks
but I'm getting an error

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

Rotating Work Schedule - 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.

Rotating Work Schedule - Herbert Seidenberg

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

Rotating Work Schedule - Herbert Seidenberg

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