Forum Discussion
Update a 52 week template to a new year
I have a spreadsheet that has 52 weeks, w/the end date each week being Friday. This goes from Jan - Dec and is used for employee PTO info. The only way I know how to make the dates update to the new year is do them 1 by 1 on the master spreadsheet. However, is there a way to highlight them all and use some "formula" to make the dates adjust to the new year?
e.g. - week ending Jan 7, 2022, I need it to be Jan 6, 2023
week ending Jan 14, 2022, I need it to be Jan 13, 2023
etc.
See attachment. Dates for the spreadsheet end each week on a Friday, but they are dated 2022.
Any way this can be done in one fell swoop?
1 Reply
This may work for you (You could simply change the year as needed):
=LET(Jan,DATE(2023,1,1),Start,Jan+CHOOSE(WEEKDAY(Jan,2),4,3,2,1,0,6,5),SEQUENCE(52,,Start,7))If you have access to Lambda, the sheet level function would be even shorter. You'd simply provide the year:
=Rollover(2023)Both options included in the attached workbook.
