Determine week number in month by date field

[Chad_Carpenter]

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!

[W_Vann_Hall]

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!

[Chad_Carpenter]

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.

[W_Vann_Hall]

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.

[Chad_Carpenter]

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

[Ryan_Buckley]

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.

[Habib_Ullah]

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 ?

[kate06]

@Habib_Ullah, there is a built-in formula for this: WEEKNUM()

[joetoscano]

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"
                )
            )
        )
    )
)