Skip to main content
Solved

Sorting entries by week # - formula not correct

T
Forum|alt.badge.img+6
  • TRBS
  • Known Participant
  • 11 replies

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!

Best answer by Ron_Daniel

@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'
)

View original
Did this topic help you find an answer to your question?

10 replies

Mike_AutomaticN
Forum|alt.badge.img+21

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
Forum|alt.badge.img+19
  • Ron_Daniel
  • Inspiring
  • 104 replies
  • Answer
  • December 30, 2024

@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'
)

VikasVimal
Forum|alt.badge.img+9
  • VikasVimal
  • Participating Frequently
  • 30 replies
  • December 31, 2024

It has troubled me several times as well.
If you want to sort by week, earliest to latest, ignore the 'WEEK' calculation. Just to a datetimediff calculation between, say, 01/01/2000 and the date field. That gives you number of days, Divide by 7 and round to 0, you'll have sequential week numbers since 01/01/2000. Good enough for a sort.

If you really want an accurate formula, go for this:

 

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

 

This formula will break it to week#53 for 29,30,31 Dec 2024, and start at week#1 for Jan 1.

If you wish to avoid having 2 different week numbers for the same week, this formula will continue the week of 29 Dec 2024 till 4th Jan 2025.

 

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'))

 

 

Ron_Daniel
1 person likes this
  • Ron_Daniel
    Ron_Daniel
T
Forum|alt.badge.img+6
  • TRBS
  • Author
  • Known Participant
  • 11 replies
  • December 31, 2024

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

T
Forum|alt.badge.img+6
  • TRBS
  • Author
  • Known Participant
  • 11 replies
  • December 31, 2024

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
Forum|alt.badge.img+28
TRBS wrote:

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?)


 

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

Adam, Consultant @ thetimesaving.company
Ron_Daniel
1 person likes this
  • Ron_Daniel
    Ron_Daniel
T
Forum|alt.badge.img+6
  • TRBS
  • Author
  • Known Participant
  • 11 replies
  • January 2, 2025

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
Forum|alt.badge.img+9
  • VikasVimal
  • Participating Frequently
  • 30 replies
  • January 3, 2025
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.

Ron_Daniel
1 person likes this
  • Ron_Daniel
    Ron_Daniel
TheTimeSavingCo
Forum|alt.badge.img+28
TRBS wrote:

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. 

 

 


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?

Adam, Consultant @ thetimesaving.company
T
Forum|alt.badge.img+6
  • TRBS
  • Author
  • Known Participant
  • 11 replies
  • January 5, 2025

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

Reply


Most helpful members this week

Terms & Conditions