Help

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

Topic Labels: Dates & Timezones Formulas
290 2
cancel
Showing results for 
Search instead for 
Did you mean: 
rileyspiller
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

rileyspiller_1-1704234402848.png

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 2
rileyspiller
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

rileyspiller_0-1704257044614.png

 

ScottWorld
18 - Pluto
18 - Pluto

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

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