Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 01, 2017 04:30 PM
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!
Nov 02, 2017 07:00 AM
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!
Nov 03, 2017 02:22 AM
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.
Nov 03, 2017 02:43 AM
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.
Nov 03, 2017 06:53 AM
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
Nov 29, 2020 08:43 PM
I know this is an old topic, but I wanted to offer a solution for others like me who were working on a similar problem.
I just needed the week of the month. For instance, November 30 is the 5th week but the next day is the 1st week of December. To get that I used this:
CEILING((DAY(TODAY())/7))
Works with any date object that you replace TODAY() with.
Mar 03, 2022 03:18 AM
Thanks for this formula as this actually worked for me but it shows the current month week number, I want to show this for number of week of the year…
Let me know how’s this gonna work ?
Sep 12, 2023 12:37 PM
@Habib_Ullah, there is a built-in formula for this: WEEKNUM()
Mar 18, 2024 03:47 PM - edited Mar 18, 2024 03:50 PM
Idk everyone's business model but I figured out a way to do it if you run on a M-F schedule and you count weeks based on what number lands on Monday (i.e. the 1st is on a Thursday, that's not the first week, but the 4th on a Monday is considered the first week):
So if 2/28 is on a Tuesday and 3/1 is on a Wednesday, that's not the first week of the month, that's the last week of February. Vice Versa, if 3/4 is on a Monday that is the first week and 3/15 is week 2 not week 3 as the code mentioned above would result in.
IF(AND({dayExtract-dayNumber}<5, {dayExtract-dayOfWeekNumber}>1), "Week 5",
IF(AND({dayExtract-dayNumber}<8, {dayExtract-dayOfWeekNumber}<5), "Week 1",
IF({dayExtract-dayNumber}<15, "Week 2",
IF({dayExtract-dayNumber}<22, "Week 3",
IF({dayExtract-dayNumber}<29, "Week 4", "Week 5"
)
)
)
)
)