Finding the Number of Fridays in a Month

[Malakai]

Hi all,

I'm wondering: Do any of you Airtable experts have an idea how I could calculate how many Fridays (or any other weekdays for that matter) there are in a given month?

[Steve_Haysom]

Hi Malakai

Do you want the result in a formula field or a script?

[Malakai]

I'm more at home with formulas :-).

[Steve_Haysom]

ok, I've tried it with formulas but can't find a solution so far.

[Malakai]

I figured that if I can find the weekday of the first day on a month, I can calculate how many days it will take to next friday. If I subtract the difference from the total number of days in a month, divide the result by 4 and add 1 for the first friday, it should work. The only question is, how do I get the weekday (as a number) from the string "friday" (or another day). Wouldn't want to use Switch, because depending on a user's locale, the weekday 1 could be sunday or monday, potentially messing up the formula.

[Steve_Haysom · Accepted solution]

You could set the first day of the week explicitly when using the weekday function, then it would not change, 

eg WEEKDAY(Date, "Monday"). Then use the VALUE function to convert this to a number: VALUE(WEEKDAY(Date, "Monday"))

[Steve_Haysom]

Here are a few formulas to help (I use camel case to prevent having to use curly brackets in field names):

FirstDayOfMonth: DATETIME_PARSE(DATETIME_FORMAT(Date, "YYYY-MM-") & "01")

FirstDayOfMonthName: DATETIME_FORMAT(FirstDayOfMonth, "dddd")

LastDayOfMonth: DATEADD(DATEADD(FirstDayOfMonth, 1, "month"), -1, "day")

Weekday: WEEKDAY(FirstDayOfMonth,"Monday")

TotalDaysInMonth: VALUE(DATETIME_FORMAT(LastDayOfMonth, "D"))

[Malakai]

True, then it would work independently of a user's locale.

[Malakai]

Thanks for this. Those are pretty much the ones I used to get to this point. I was just stuck trying to convert a user's input (Monday - Sunday) into a WEEKDAY, to perform the final step of the calculation.