Skip to main content

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

 

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?

 

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

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

Terms & ConditionsAccessibility statement