PDA

View Full Version : How might I create a 4 week rota?

Joe 90
16-09-2008, 12:19
Afternoon,
I've got this rota that rotates on a 4 week basis - rota (http://www.marksweb.co.uk/images/uploads/rota.jpg)

To make life easy i'd love to get it to automate throughout the year as to who is doing each day every week.

I'm sure Excel could do this, but i've got no idea how. Any ideas?
kaiowas
16-09-2008, 13:09
If you've got a date in A1 then =MOD(INT((A1+5)/7),4) will return a number from 0 to 3 corresponding to a week number within your rota. Allocating tasks based on a combination of this number and the weekday should then be pretty straightforward.
Joe 90
16-09-2008, 13:25
its been too long since i used excel 'properly'... used to make animations on graphs of perspective change n stuff, but now thats got me stumped... going to have to have a play around...
kaiowas
16-09-2008, 13:41
I'd probably have a second sheet with a copy of the 4 week rota on it then do lookups on that table on the live sheet. Will make changing the rota down the line easy too that way.

If I was at work I'd do the whole thing just to look busy but I'm not :p
JUMPURS
16-09-2008, 18:28
Recurring appointments in outlook? Then print it off in natty little calendar format?
Joe 90
16-09-2008, 19:14
yeah i was about to do it in outlook but then it kept moaning about connecting to the exchange server and i've not got it setup.

its always nice to use excel though, exercise the brain and get a bit creative at the same time.
Joe 90
18-09-2008, 11:39
If you've got a date in A1 then =MOD(INT((A1+5)/7),4) will return a number from 0 to 3 corresponding to a week number within your rota. Allocating tasks based on a combination of this number and the weekday should then be pretty straightforward.

right, actually decided to give this a bash today.

when it comes to allocating each day, is there a simpler method than a massive if statement?
Blighter
18-09-2008, 18:42
I've also found this site better for getting serious replies :D
Joe 90
19-09-2008, 16:33
I've also found this site better for getting serious replies :D

and you've been no help in either thread :p
Blighter
20-09-2008, 00:40
Thanks :)
kaiowas
23-09-2008, 10:53
Found a spare 10 minutes at work this morning:

http://www.kaiowas.co.uk/rota.xls

You can extend the calendar down as far as you want to, names and the actual rota can be changed from the "rota" sheet.
Joe 90
25-09-2008, 22:20
you sir, are a legend :D

thanks. i'll have a look at how its done.