Sorting entries by week # - formula not correct

[TRBS]

I am trying to sort by the Week # of the year that an entry is assigned on my base. Its sorted by year, then by week - so 2024, week 1 through 52, etc.

However, for anything this week up until the 31st, its showing up as the 1st week of Dec 2024 (which confuses me as you would think it'd be 1st week of 2025?). I would like that to show up as the last week of Dec 2024 (week 52) or even Week 1 2025.

Formula is:

WEEKNUM((Assigned), "Monday")

It is pulling data from the "Assigned" column, which is listed in the format: 12/30/24 11:00am

Thanks!

[Mike_AutomaticN]

Hey @TRBS!

Would you mind sharing (i) a copy and paste of your formula; and (ii) a screenshot of the example mentioned?

Thanks!
Mike, Consultant @ Automatic Nation 

[Ron_Daniel · Accepted solution]

@TRBS ,

Week numbering is tricky, because Airtable's WEEKNUM() doesn't follow ISO week date system standards.

Airtable will always count the week containing January 1 as Week 1, regardless of how many days from the new year it includes.
But the ISO standard is that weeks always start on Monday, and that Week 1 is the first week containing at least four days of the new year. A week's year depends on what year that week's Thursday is in.

I believe you can get the results you're looking for with this formula instead of WEEKNUM():

DATETIME_FORMAT(
SET_TIMEZONE(DATEADD((Assigned), -2, 'days'), 'GMT'),
'WW'
)

[TRBS]

@Ron_Daniel That worked!!! Thank you so much!

[TRBS]

Actually @Ron_Daniel what that did is set Wednesday as the top of the week, so now all the weeks throughout the year are starting on Wednesday. Is there a way to only distinguish the change for the beginning of the year and the end of the year? so that it only includes 2024 weeks (2025 will start a new week 1, even if its not a total of 7 days in the week?)

[TheTimeSavingCo]

Hmm does this look right?

IF(
  ROUNDUP((DATETIME_FORMAT(Date, "DDD") + 0)/7, 0) = 53,
  1,
  ROUNDUP((DATETIME_FORMAT(Date, "DDD") + 0)/7, 0)
)

Link to base

[TRBS]

For some reason, anything submitted on 12/29, 12/30 or 12/31/24 is showing up as "week #1 2024". Which is also strange.