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