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”.

Hi Katy,

If Date 1 is in the future and you know that it will not be further away than next year, you could probably get away with this formula:

IF(YEAR({Date 1})>YEAR(NOW()),
52 + WEEKNUM({Date 1})-WEEKNUM(NOW()), WEEKNUM({Date 1})-WEEKNUM(NOW()))

In other words, you check if the year of date1 is the same as today and if not, you add 52 to the week number of Date 1 (or maybe 53, depending on the current year).

Now, I am not sure WEEKNUM is the best option to calculate a difference in weeks. You may get a better result by calculating the number of days and dividing by 7.

DAYS({Date 1},NOW())/7

This will likely give you a decimal number. If you want a round number:

ROUND(DAYS({Date 1},NOW())/7,0)

What if we could break free of the Formula column and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.