Rota Projection

[Guest]

We have a rolling 5 week rota with a week starting on sunday an example of
which is shown below.
Sun Mon Tue Wed Thu Fri Sat
1 Rest Rest A2 A2 A2 A2 A2
2 Rest A1 A1 A1 A1 A1 Rest
3 Rest B2 B2 B2 B2 Rest B1
4 B1 B1 B1 Rest Rest B2 B2
5 B2 B2 Rest B1 B1 B1 Rest

Our pay period is every 4 weeks and a duty statement is submitted for this
period. What I would like to do is that given a start date of the rota is to
be able to enter a future date and to have my duties for the 4 next weeks to
be displayed.
Any help would be really appreciated.

 

[Sandy Mann]

Here's one way:

In an unused area of your spreadsheet create a lookup table of lookup
numbers and the shifts with the date, (n the past) of when the shift pattern
started in that order:

Start Date_____1/1/2007
___1_________Rest
___2_________Rest
___3_________A2
___4_________A2
___5_________A2
___6_________A2
___7_________A3
___8_________Rest
___9_________A1
___10________A1
___11________A1
etc. down to Number 35 in row 36

I used J1:K36 which may be hidden afterwards if you want.

Then in A2 I entered the date of the Sunday start of the four week period
you are interested in and in B2:H2 weekday lablels Sun through Sat.

B2 has the formula:
=$A$2+(COLUMN()-COLUMN($B$1)) which is copied across to H2

B5 has the formula: =B3+7 which in turn is copied across to H5 and then the
range B5:H5 copied to B7:H7 & B9:H9 and the cells in these cells were custom
formatted as "d" (without the quotes), to give the date in the cell but to
display the calender date number.

Finally in B4 enter the formula:
=VLOOKUP(MOD(B3-$K$1,35)+1,$J$2:$K$36,2)
which was copied across to H4 and then to rows 6,8 & 10 which will display
the shift.

Post back if you need further help.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Hi Sandy
Thanx a million, its working a treat. Greatly appreciated!!!

 

[Sandy Mann]

Hi gramps,

Glad you got it working.

It was actually an adaptation of a sheet that I wrote for my daughter's CID
shifts which did more than just the shifts. For the record, if for no other
purpose, to return just the shifts you only need the date and list in column
K and then use the formula:

=INDEX($K$2:$K$36,MOD(B2-$K$1,35)+1)
in place of the VLOOKUP() formula.

Us Gramps have to stick together <g>
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk