#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 46
Default Rota Projection

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.
--
Al
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,345
Default Rota Projection

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


with @tiscali.co.uk


"gramps" wrote in message
...
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.
--
Al



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 46
Default Rota Projection

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


"gramps" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,345
Default Rota Projection

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


with @tiscali.co.uk


"gramps" wrote in message
...
Hi Sandy
Thanx a million, its working a treat. Greatly appreciated!!!
--
Al


"gramps" wrote:

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
forecast or projection Dena J Excel Worksheet Functions 0 March 16th 07 07:39 PM
Month End Projection formula? sueshe Excel Worksheet Functions 8 November 28th 06 02:13 PM
Rota chris.howes Excel Discussion (Misc queries) 1 July 24th 06 12:30 PM
Projection of values call_Vishwa Excel Worksheet Functions 0 May 8th 06 11:23 AM
dealing with a projection Larry Holt Charts and Charting in Excel 0 February 15th 06 04:03 PM


All times are GMT +1. The time now is 06:04 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"