Forum Discussion
Automatically create dates when changing weeks
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
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
6 Replies
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))
- 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 THi 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
