Excel calendar wizards

[Yella Fella]

Hey all,

So I'm trying to learn about doing a rota schedule and basing it on this template https://templates.office.com/en-gb/employee-absence-schedule-tm03987167.

What I don't understand is how to automate the month by month in one spreadsheet rather than different monthly tabs. I'm already stuck on this days format as why it doesn't change when dragging the cells along... is this a manual process only?

=TEXT(WEEKDAY(DATE(CalendarYear,1,1),1),"aaa")

Also I'm not sure how they got the actual date numbers to change when changing the year. What formula is that and how is it linked?

I'm looking to create the whole year on one sheet.

 

[batfink]

=TEXT(WEEKDAY(DATE(CalendarYear,1,1),1),"aaa")

This formula will only ever return 1st January on whatever year you have entered.

'CalendarYear' is a name they have defined which refers to the year cell (AJ4)
1,1 refers to the month (1 = January) and day (1 = 1st)

To make it dynamic, you need to have a reference that changes every time you drag the formula along. You will notice that their formula increases the number by one in each cell manually.

You could change it to this for January and that would work when you drag it across:

=TEXT(WEEKDAY(DATE(CalendarYear,1,E6),1),"aaa")

E6 becomes F6, G6 etc when dragged across.

February would then start

=TEXT(WEEKDAY(DATE(CalendarYear,2,E6),1),"aaa")

Except you want all months on one tab so the reference to E6 would change to whatever row the days are in your February table.

 

[adolf hamster]

i'm guessing you want something like:

start date: monday tuesday wednesday thursday friday
28/12/2020
4/01/2021
11/01/2021

etc?

in which case you can put the first date of the year (dates i've put are for monday being the first day of the year) in the top left then have the remainder of the cells below being "=A5+7" (that's with A5 being the first cell in the column)

then you can drag it down as many rows as you need, it'll just roll over when the next year comes around.

if you want to say have the months coloured for easy distinction then you could have another column that starts with "=month(a5)" which will give you the number of the month which you can then use for conditional formatting (eg using one of the colour scales). that'll only work on the start date (so in the above example "january" wouldn't start until the second line) in which case you could do "=month(a5+5)" which would calculate what month it was on the friday of each week.

edit: ocuk doesn't like a bunch of spaces

 

[Yella Fella]

This formula will only ever return 1st January on whatever year you have entered.

'CalendarYear' is a name they have defined which refers to the year cell (AJ4)
1,1 refers to the month (1 = January) and day (1 = 1st)

To make it dynamic, you need to have a reference that changes every time you drag the formula along. You will notice that their formula increases the number by one in each cell manually.

You could change it to this for January and that would work when you drag it across:

=TEXT(WEEKDAY(DATE(CalendarYear,1,E6),1),"aaa")

E6 becomes F6, G6 etc when dragged across.

February would then start

=TEXT(WEEKDAY(DATE(CalendarYear,2,E6),1),"aaa")

Except you want all months on one tab so the reference to E6 would change to whatever row the days are in your February table.

If I do it with February would that be dynamic or still have to manually enter in the 2 in CalendarYear,2,E6.

I have to hand this over once done so want to make it easy for them in terms of stretching out the rota as time goes along.

i'm guessing you want something like:

start date: monday tuesday wednesday thursday friday
28/12/2020
4/01/2021
11/01/2021

etc?

in which case you can put the first date of the year (dates i've put are for monday being the first day of the year) in the top left then have the remainder of the cells below being "=A5+7" (that's with A5 being the first cell in the column)

then you can drag it down as many rows as you need, it'll just roll over when the next year comes around.

if you want to say have the months coloured for easy distinction then you could have another column that starts with "=month(a5)" which will give you the number of the month which you can then use for conditional formatting (eg using one of the colour scales). that'll only work on the start date (so in the above example "january" wouldn't start until the second line) in which case you could do "=month(a5+5)" which would calculate what month it was on the friday of each week.

edit: ocuk doesn't like a bunch of spaces

I was wanting to get it close to the initial template as was interested in having it as Mon, Tue, Wed etc. Also curious how they got the numbers in the row beneath to dynamically change as well.

Looking to expand the months horizontally in one tab.

 

[adolf hamster]

If I do it with February would that be dynamic or still have to manually enter in the 2 in CalendarYear,2,E6.

I have to hand this over once done so want to make it easy for them in terms of stretching out the rota as time goes along.



I was wanting to get it close to the initial template as was interested in having it as Mon, Tue, Wed etc. Also curious how they got the numbers in the row beneath to dynamically change as well.

Looking to expand the months horizontally in one tab.

ahh yes, overlooked this is for a group of people.

in that case you could do:

start date:| weekday | john | billy | Margatet | .......
01/01/2021 | friday |
02/01/2021 | Saturday |
03/01/2021 | Sunday |

first column is as before "=a5+1"
next column could be an index/match to a simple table on a hidden sheet "=index([array with monday/tuesday/wednesday etc in],match(weekday(a5),[array with 1,2,3 corresponding to the day you want],0))"

segregating by month might be a tad trickier, way i'd do it is again with a hidden calculations sheet, much easier if your organization prefers going by the week number ie "week 32"