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. 

[VikasVimal · Accepted solution]

IF(AND(DATETIME_FORMAT(Date,'M')=12,WEEKNUM(Date)=1),WEEKNUM(DATEADD(Date,-6,'days'))+1,WEEKNUM(Date))

This takes separates the dates till 31st Dec to last week of previous year and 1 Jan is 1st week of new year. But the week itself is split into 2024-53, and 2025-01.

IF(
  DATETIME_DIFF(
  Date,
    DATETIME_PARSE(DATETIME_FORMAT(
      Date,'YYYY')&"/01",'YYYY/WW'),'weeks')=0,
  DATETIME_DIFF(
  Date,
    DATETIME_PARSE(DATETIME_FORMAT(
      DATEADD(Date,-6,'days'),'YYYY')&"/01",'YYYY/WW'),'weeks'),
  DATETIME_DIFF(
  Date,
    DATETIME_PARSE(DATETIME_FORMAT(
      Date,'YYYY')&"/01",'YYYY/WW'),'weeks'))

This one continues the last week of the previous year into the new year, so, if week started on 29th Dec, it'll continue till 4th Jan as 2024-52.

[TheTimeSavingCo]

Yeap, that would be week #1 of 2025 like you mentioned in your original post! 

If that doesn't seem right to you, could you provide a screenshot with dates from 16th Dec 2024 to 14th Jan 2025, and indicate what you would want the output to be?

[TRBS]

Thank you! I'll take the week 53 to keep it in 2024. Thank you all!