Determine week number in month by date field


#1

I have a date field in my table named “Date Paid”. I would like to determine the week number of the month that date falls into in another field. Example, 2017-11-01 falls into week one of November, 2017-11-06 falls into week 2. Is there a formula that will do this? I know in VBA I can write a function to do this but want to do this in Airtable.

I’ve been able to determine the month “Date Paid” falls under pretty easily with,

MONTH({Date Paid})

but don’t see any built in functions for week number of the month

Thanks for any tips you can provide for this!


#2

While I suspect ‘week of the month’ isn’t likely to be a canned solution because of differences in how different people might calculate it (for instance, are we talking first full week of the month, or first calendar week containing at least one day of the month? counting weekends or weekdays only? on what day does the week start? and so on), there is a whole raft of interesting derivations possible using DATETIME_FORMAT() with the appropriate format specifier. In fact, one could probably put together a function that returns ‘week of the month’ as you appear to mean it:

VALUE(DATETIME_FORMAT(Date,'w'))-
VALUE(DATETIME_FORMAT(
DATETIME_PARSE('01'&MONTH(Date)&YEAR(Date),'DDMMYYYY'),'w'))+1

I just tossed that together and it seems to work, but you might want to bang on it a bit.

Hope this helps!


#3

Thank you for getting me started on this, much appreciated. The function appears to work for MONTH with two digits (i.e. > 10, October) but gives #ERROR! for other MONTH < 10 so I have some playing to do. My date field is also called {Date Paid} so I made that change as well.


#4

My bad – I always get burned by that.

Instead of MONTH(Date) try DATETIME_FORMAT(Date,'MM'). That should get you two-digit month values year-round.


#5

I like to think that would have come to me eventually… But I do appreciate you doing all the thought provoking work for me!

This is my final formula:

VALUE(DATETIME_FORMAT({Date Paid},'w'))-
VALUE(DATETIME_FORMAT(DATETIME_PARSE('01'&DATETIME_FORMAT({Date Paid},'MM')&YEAR({Date Paid}),'DDMMYYYY'),'w'))+1