Assign week number per month

[Tim_Young]

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.

[W_Vann_Hall]

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…