Auto-populate ongoing rolling rota by date

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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
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 ?
 

wintye

New Member
Joined
Apr 15, 2013
Messages
15
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
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
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>
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005

ADVERTISEMENT

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>
 

wintye

New Member
Joined
Apr 15, 2013
Messages
15
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
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005

ADVERTISEMENT

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
 

wintye

New Member
Joined
Apr 15, 2013
Messages
15
Brilliant, that's done it, thank you so much. I truly appreciate your help and patience.

Have a great (what's left of) weekend :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,881
Messages
5,598,636
Members
414,251
Latest member
oExcel

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
Top