WEEKNUM over multiple years

Hi! I have a date column and another column that shows the number of weeks to that date. Since WEEKNUM only works for the current year, I’m trying to find a workaround. Is there a way to automatically set a second date column to a specific date based on the original date field? That’s the only thing I can get to work.

Here’s my example -

  • Column 1 - Date 1 - date field
  • Column 2 - Weeks to Date 1 - formula - “IF(YEAR({Date})=2023, WEEKNUM({Date 1})-WEEKNUM(TODAY()), WEEKNUM({Date 2})-WEEKNUM(TODAY()))”
  • Column 3 - Date 2 - date field

If I manually set the Date 2 date to either Jan 1, 2023 or Jan 1, 2024, then I can get the formula in Column 2 to work properly. However, it’s easier to change this formula on the 1st of every year vs manually adding Jan 1, 2023 or Jan 1, 2024 to every single item when it’s created. So is there a way to automatically set Column 3 with either Jan 1, 2023 or Jan 1, 2024 based on Column 1?

  • Column 1 - “Nov 1, 2023” would auto set Column 3 to “Jan 1, 2023”.