Auto-populate ongoing rolling rota by date

W

wintye

New Member
Joined
Apr 15, 2013
Messages
15
Hi All,

My husband has an 8 week rolling rota and I'd like to create a spreadsheet where he can enter in the year and it will automatically fill the dates he's working and off.

There are actually 8 teams, each with a different 8 week rolling rota so it would be good for a drop down to show the different groups data also.

I've been looking into for a few hours and can't find what formula's I need to be searching on in order to start it.

I have a spreadsheet where the dates are automatically populating dependent on 1 cell where I enter a start date.

Can anyone advise? It didn't seem like something that would be too complicated but I just can't wrap my head around it.

Many thanks in advance
Wintye
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
8 weeks is 56 days so if he works days 1234 and rests 567 it is easy for any starting date - assume he is in team1 and team2 rest 12 and work 3456 rest 78 then that is easy - so drop down = team name and dates are displayed - is this what you want ?
 
Upvote 0
Hi Oldbrewer,

Thanks for your reply.

That's exactly what I'm thinking, thank you. I just don't actually know how to tie that in with say today's date for example.

The 8 weeks for team 1 looks like this (I'm not concerned about the layout if it needs to change):

SunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSat
25-Mar26-Mar27-Mar28-Mar29-Mar30-Mar31-Mar01-Apr02-Apr03-Apr04-Apr05-Apr06-Apr07-Apr08-Apr09-Apr10-Apr11-Apr12-Apr13-Apr14-Apr15-Apr16-Apr17-Apr18-Apr19-Apr20-Apr21-Apr
WW W OW W W W W OW W W W W OO W W W O O W W W W W O
SunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSat
22-Apr23-Apr24-Apr25-Apr26-Apr27-Apr28-Apr29-Apr30-Apr01-May02-May03-May04-May05-May06-May07-May08-May09-May10-May11-May12-May13-May14-May15-May16-May17-May18-May19-May
W W W OO W W W OO W W W W W O W W W W OO W W W W OO

<colgroup><col span="15"><col><col span="10"><col span="2"></colgroup><tbody>
</tbody>


I'd like to have a cell where a year can be entered and the cells above will auto-populate and view like the above (e.g. the date, the day and if it's a working day or off). The plan is then, that as long as the shift pattern doesn't change, it doesn't matter what year you enter into the one cell, it will pull through the dates for that year and what will be working or off.

It seems like it must be something simple but I can't wrap my head around what I need to do. I did find something online that was VBA code but I'm very unfamiliar with code so am hoping to find out how to do it a different way.

Thank you so much for your help.

Many thanks
Wintye
 
Upvote 0
25/03/2018W
28/03/2018O
29/03/2018W
03/04/2018O
04/04/2018Wcol J
09/04/2018O
11/04/2018W
14/04/2018Orow 825/03/2018W#####
16/04/2018W26/03/2018W
21/04/2018O27/03/2018W
22/04/2018W28/03/2018O
25/04/2018O29/03/2018W
27/04/2018W30/03/2018W25/03/201826/03/201827/03/201828/03/201829/03/201830/03/201831/03/2018
30/04/2018O31/03/2018WWWWOWWW
02/05/2018W01/04/2018W
07/05/2018O02/04/2018W01/04/201802/04/201803/04/201804/04/201805/04/201806/04/201807/04/2018
08/05/2018W03/04/2018OWWOWWWW
12/05/2018O04/04/2018W
14/05/2018W05/04/2018W08/04/201809/04/201810/04/201811/04/201812/04/201813/04/201814/04/2018
18/05/2018O06/04/2018WWOOWWWO
20/05/2018W07/04/2018W
23/05/2018O08/04/2018W15/04/201816/04/201817/04/201818/04/201819/04/201820/04/201821/04/2018
24/05/2018W09/04/2018OOWWWWWO
29/05/2018O10/04/2018O
30/05/2018W11/04/2018W
04/06/2018O12/04/2018W
06/06/2018W13/04/2018W
09/06/2018O14/04/2018O
11/06/2018W15/04/2018O
16/06/2018O16/04/2018W
17/06/2018W17/04/2018W
20/06/2018O18/04/2018W
22/06/2018W19/04/2018W
25/06/2018O20/04/2018W
27/06/2018W
02/07/2018O
03/07/2018W
07/07/2018O
09/07/2018W#####
13/07/2018O=VLOOKUP(J8,MYTABLE,2)
A1 to B40 is named "mytable"
you could make it "team1" and make D1 to E40 "team2"

<colgroup><col><col><col><col><col><col><col span="3"><col><col span="2"><col span="2"><col span="2"><col span="2"><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
25/03/2018W
28/03/2018O
29/03/2018W
03/04/2018O
04/04/2018Wcol J
09/04/2018O
11/04/2018W
14/04/2018Orow 825/03/2018W#####
16/04/2018W26/03/2018W
21/04/2018O27/03/2018W
22/04/2018W28/03/2018O
25/04/2018O29/03/2018W
27/04/2018W30/03/2018W25/03/201826/03/201827/03/201828/03/201829/03/201830/03/201831/03/2018
30/04/2018O31/03/2018WWWWOWWW
02/05/2018W01/04/2018W
07/05/2018O02/04/2018W01/04/201802/04/201803/04/201804/04/201805/04/201806/04/201807/04/2018
08/05/2018W03/04/2018OWWOWWWW
12/05/2018O04/04/2018W
14/05/2018W05/04/2018W08/04/201809/04/201810/04/201811/04/201812/04/201813/04/201814/04/2018
18/05/2018O06/04/2018WWOOWWWO
20/05/2018W20/5/18 got by formula =a1+5607/04/2018W
23/05/2018O08/04/2018W15/04/201816/04/201817/04/201818/04/201819/04/201820/04/201821/04/2018
24/05/2018Wjust copy the 56 block pattern09/04/2018OOWWWWWO
29/05/2018Odown for as many years as you like10/04/2018O
30/05/2018W11/04/2018W
04/06/2018O12/04/2018W
06/06/2018W13/04/2018W
09/06/2018O14/04/2018O
11/06/2018W15/04/2018O
16/06/2018O16/04/2018W
17/06/2018W17/04/2018W
20/06/2018O18/04/2018W
22/06/2018W19/04/2018W
25/06/2018O20/04/2018W
27/06/2018W
02/07/2018O
03/07/2018W
07/07/2018O
09/07/2018W#####
13/07/2018O=VLOOKUP(J8,MYTABLE,2)
A1 to B40 is named "mytable"
you could make it "team1" and make D1 to E40 "team2"

<colgroup><col><col><col><col><col><col><col span="3"><col><col span="2"><col span="2"><col span="2"><col span="2"><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Hiya,

Thank you for the above. I apologise for the delay in getting back to you, been busy with work.

I'm not sure I understand the above. In column A there are dates missing?
I really appreciate your help but I'm really not sure on what the above is doing.

Claire
 
Upvote 0
column A does not need to have EVERY date - the column to the right has every date and the formula refers to the first column to determine W or O

so 27 march refers to first column and determines it is a W day
 
Upvote 0
Brilliant, that's done it, thank you so much. I truly appreciate your help and patience.

Have a great (what's left of) weekend :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA to automatically populate rolling rota - Help?!
Replies
0
Views
521
samboco
S
S
  • Question
Automatic accrual calculations by day with accrual rate?
Replies
0
Views
236
Sunshine8790
S
A
  • Question
How to auto-populate a number entered in one cell to another depending on date range
Replies
9
Views
98
awoohaw
awoohaw
Juddman79
  • Solved
Testing schedule based on frequency and date
Replies
3
Views
296
felixstraube
F
C
  • Question
Duplicates with different data to combine with VBA
Replies
2
Views
108
chipsworld
C

Forum statistics

Threads
1,214,813
Messages
6,121,706
Members
449,048
Latest member
81jamesacct

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top