Skip to main content

I found this formula here that has been working OK but I need to adjust it slightly and I’m not sure how.


VALUE(DATETIME_FORMAT({Show Date},‘w’))-

VALUE(DATETIME_FORMAT(DATETIME_PARSE(‘01’&DATETIME_FORMAT({Show Date},‘MM’)&YEAR({Show Date}),‘DDMMYYYY’),‘w’))+1


This gives me week numbers per month and then resets for the next month but I need Sunday to (and including) Saturday to be assigned the same week number. The way this formula is working right now, Saturday is being assigned to a new week. For example…


1st Sunday-Friday of the month = week 1, Saturday starts week 2


I hope this makes sense.


Your formula works fine for me here – which makes me think the problem is probably related to GMT processing. (Would you be in the UK?) If I configure {Show Date} to use a time field and set the timestamp so that {Show Date} and GMT date are off, I can reproduce your problem.


Unfortunately, as far as I know the only way to correct this is to use SET_TIMEZONE(). (I thought Airtable used to support the GMT toggle on datetime fields without time stamps — but if so, it doesn’t any longer. I’ve messaged Support to ask.)


To use SET_TIMEZONE(), you’d need to do this:


VALUE(

    DATETIME_FORMAT(

        SET_TIMEZONE(

            {Show Date},

             TZ]

            ),

        'w'

        )

    )-VALUE(

        DATETIME_FORMAT(

            DATETIME_PARSE(

                '01'&DATETIME_FORMAT(

                    SET_TIMEZONE(

                        {Show Date},

                         TZ]

                        ),

                    'MM')&DATETIME_FORMAT(

                        SET_TIMEZONE(

                            {Show Date},

                             TZ]

                            ),

                        'YYYY'

                        ),

                    'DDMMYYYY'

                ),

            'w'

            )

        )+1

(I think that’s right, but I haven’t tested.)


You’ll need to replace ‘ TZ]’ with your local timezone, chosen from this list.


If that doesn’t fix it, let me know and I’ll flail about wildly again…

Reply


Terms & Conditions