Skip to main content

Airtable doesn't count December 31, 2023 in the correct week

  • January 2, 2024
  • 2 replies
  • 29 views

Forum|alt.badge.img+4

I'm using a date field {Date Processed} and a formula field {Report Periods} to interpret that date and produce a comma separated list to find the year, quarter, month, week, and day. I use this for reporting. Check out this screenshot showing how 12/31/2023 (USA time format) is messing up the formula.

The final day of the year should be in the 53rd week of the year. Instead it's starting over at week 1. I believe this is a bug in Airtable. But please let me know if there is any known solution. 

The formula for the {Report Periods} field is:

IF(
    {Date Processed},
    SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
        DATETIME_FORMAT({Date Processed}, "YYYY-ⓨ, YYYY-ⓠ-Q, YYYY-ⓜ-MM, YYYY-ⓦ-ww, L"), 
        "ⓨ", "year"), "ⓠ", "quarter"), "ⓜ", "month"), "ⓦ", "week"
    )
)

 

2 replies

Forum|alt.badge.img+4
  • Author
  • New Participant
  • January 3, 2024

Following up... I tried another method using a new formula field called Week Number:

WEEKNUM({Date Processed})

You can see here that Airtable is counting December 31st, 2023 as week number 1. This should clearly be week 53... what gives. Is this an Airtable bug?

 


ScottWorld
Forum|alt.badge.img+35
  • Genius
  • January 3, 2024

It’s strange, but it’s actually how the week numbering system works:

 https://en.wikipedia.org/wiki/Week#Week_52_and_53