Automatically create dates when changing weeks

[M-Tommo2426]

I have a work roster, I am trying to automatically re-populate dates in cells Saturday through to Friday when I change cell B3 which is the week I'm in, currently is 1, when I change B3 to 2 (week 2) I need the dates to change to correct dates, also once I get to the end of the 12 week roster I change back to week to week 1 and the 12 week roster starts again, dates should carry on from week 12.

Is there anyone out there that could help me with a formula to do this it would be much appreciated

I look forward to any responses

cheers

Mike 

[#2]

Hi @M-Tommo2426 

here is one possible solution. You would also need a column for the year:

=DATE(A4;1;7*B4-3-WEEKDAY(DATE(A4;;);3))

Depending on your local settings, you might need to replace the ; with ,

=DATE(A4,1,7*B4-3-WEEKDAY(DATE(A4,,);3))

[#3]

Hi DTE,
thanks for your reply, your formula works great, the only problem I see is when the week 12 roster finishes week one starts again but date doesn't carry on from week 12 like I want it to, it goes back to early January, I want the date to carry on to when week 12 finishes which is Sat 26th March 2022 - Friday 1st April,
NOTE: Week 1of the roster started 1st January 2022 which was a Saturday.

Can this be done??? I hope so

Cheers
Mike T

[#4]

Hi Mike,

my formula considers the European rules for calender weeks (ISO), therefore it gives Monday, 3rd of January for the first day of calendar week 3. And I know the rules in the US are different.

But I'm getting confused now when you say after 12 weeks roster the week 1 will start again...

Maybe you can upload an example sheet with some explanations in it, to make it clearer.

Cheers,

Martin 

[#5]

Thanx for getting back to me Martin, this is my first post in this community forum and if I knew how to attach a sample spreadsheet here I would have, I was able to solve my issue through another excel forum, if you still want to know how it all works please let me know how to attach my spreadsheet I'm only too happy to do so.

cheers and thanx for all your help

Mike T 

[#6]

Hi @M-Tommo2426, would you mind posting your solution here for others who may have the same problem in the future? Thank you!

[#7 · Accepted solution]

@Grahmfs13 

This issue has now been solved, see attachment of my 12 week rotating roster,

how it works is:- Cell B3 shows week 1 of a 12 week roster, you'll also notice the date in cell C3 is 1-Jan-22 and in cell K4 is an employee name, for week 2 of the 12 week roster just change cell B3 to 2, now notice the date in cell C3 is 8-Jan-22 and in cell K4 is a different name, the names drop down one each week change and the one at the bottom goes to the top and so and so on. now one you get to week 12 the next week reverts back to week one to start the 12 week roster all over again, once you change cell B3 back to week 1 then go to cell A3, this is a dropdown list of 1-5, choose 2 as this is the 2nd of the rotating roster, once you have done this notice the date in cell C3 is has carried on from week 12 and is now the 26-Mar-22, these dates now show the correct dates for each rotating roster for the whole year and which employee is doing which roster for each week of any roster.

hope this all makes sense to  you

cheers, Mike T