Automatically create dates when changing weeks

[M-Tommo]

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 as shown in sample spreadsheet, see attached,
also once I get to the end of the 12 week roster I change week to week 1 and the date should carry on from week 12.



cheers
Mike T

[CARACALLA]

C3=IFERROR(AGGREGATE(15,6,ROW(INDIRECT($N$2&":"&$O$2))/(WEEKNUM(ROW(INDIRECT($N$2&":"&$O$2)),16)=$B$3),COLUMNS($C$2:C2)),"")

Copy across

[M-Tommo]

C3=IFERROR(AGGREGATE(15,6,ROW(INDIRECT($N$2&":"&$O$2))/(WEEKNUM(ROW(INDIRECT($N$2&":"&$O$2)),16)=$B$3),COLUMNS($C$2:C2)),"")

Copy across

Hi Caracalla, your formula works great for the length of the rotating roster which is 12 weeks but when I change week back to week 1 to start a new 12 week roster
the date goes back to 01-Jan-22 where date should carry on from week 12 should read 26-Mar-22 not 01-Jan-22. hope its an easy fix for you, thanx again your help is much appreciated

look forward to your reply


cheers
Mike

[JeteMc]

Excel would have to have some way of knowing that the week is week 1 of the second period of time.
Perhaps the following will help:
1. Select the period (1,2,3,4,5) in cell A3
2. Paste the following formula into cell A4: =B3+12*(A3-1)
3. Modify CARACALLA's formula to read: =IFERROR(AGGREGATE(15,6,ROW(INDIRECT($N$2&":"&$O$2))/(WEEKNUM(ROW(INDIRECT($N$2&":"&$O$2)),16)=$A4),COLUMNS($C$2:C2)),"")
Let us know if you have any questions.

[nick.williams]

If Week 1 start date is in N2 (manual input), try:

=$N$2+7*($B3-1)+COLUMN(C3)-3

[M-Tommo]

Thanx you guys, between Caracalla, JetMc and nick.williams, you guys were able to solve my date issue

thanx again, you guys are fantastic


Cheers
Mike T

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.