Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
forecast or projection | Excel Worksheet Functions | |||
Month End Projection formula? | Excel Worksheet Functions | |||
Rota | Excel Discussion (Misc queries) | |||
Projection of values | Excel Worksheet Functions | |||
dealing with a projection | Charts and Charting in Excel |