Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
Jan 06, 2023 03:40 PM
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?
Solved! Go to Solution.
Jan 07, 2023 02:52 AM
You could set the first day of the week explicitly when using the weekday function, then it would not change,
Jan 07, 2023 01:15 AM - edited Jan 07, 2023 01:16 AM
Hi Malakai
Do you want the result in a formula field or a script?
Jan 07, 2023 01:37 AM
I'm more at home with formulas :-).
Jan 07, 2023 02:22 AM
ok, I've tried it with formulas but can't find a solution so far.
Jan 07, 2023 02:28 AM
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.
Jan 07, 2023 02:52 AM
You could set the first day of the week explicitly when using the weekday function, then it would not change,
Jan 07, 2023 02:59 AM
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"))
Jan 07, 2023 03:02 AM
True, then it would work independently of a user's locale.
Jan 07, 2023 03:03 AM
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.