Roll over weeknumber --> date at year

Bryan Moss

I have been able to piece together a formula that is mostly working and I need some assistance to get the final piece of it.

I have a [Calendar Week (1-52)] column that is mapping a date to a calendar week. It successfully rolls over once it hits 52 and resumes at 1.

I need to now map that [Calendar Week (1-52)] column to the start date of that calendar week (assuming Monday start). I am able to map it to the date but I also want it to roll the year to 'next year' if I hit week 52, the 1.

Anyone have any ideas or tips on how I can enhance this formula/scenario to be able to roll the calendar year when the week rolls?

Current weeknumber to date formula =IF([Calendar Week (1-52)]@row <= 52, DATE(2020, 12, 30) + (([Calendar Week (1-52)]@row) * 7 - 2), "") Side disclosure: I do not know why 2020 works in this formula and 2021 does not. It is probably staring me in the face but I am not sure. I would love to make it aware of 'current' year but haven't been able to figure that out.

Example of my columns/weeknumber/date that I am trying to make roll into 2022, or future years in the attached. Note at Week 1 it starts over at the beginning of January 2021 and I am trying to get it to roll over to January 2022.

Thank you in advance for any tips or advise you may have to make this work!

screenshot

Paul Newcome

Are you able to base everything off of the Project Week column? Basically you would use a VALUE/RIGHT function to pull the project week number, multiply by 7 to get the number of days from the start date, then add that to the start date. Then you can use a very basic WEEKNUMBER formula to output the calendar week number based on the date.