Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Finding the Number of Fridays in a Month

Solved
Jump to Solution
10802 9
cancel
Showing results for 
Search instead for 
Did you mean: 
Malakai
6 - Interface Innovator
6 - Interface Innovator

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?

1 Solution

Accepted Solutions
Steve_Haysom
8 - Airtable Astronomer
8 - Airtable Astronomer

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

See Solution in Thread

9 Replies 9
Steve_Haysom
8 - Airtable Astronomer
8 - Airtable Astronomer

Hi Malakai

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

Malakai
6 - Interface Innovator
6 - Interface Innovator

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

Steve_Haysom
8 - Airtable Astronomer
8 - Airtable Astronomer

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

Malakai
6 - Interface Innovator
6 - Interface Innovator

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
8 - Airtable Astronomer
8 - Airtable Astronomer

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
8 - Airtable Astronomer
8 - Airtable Astronomer

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
6 - Interface Innovator
6 - Interface Innovator

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

Malakai
6 - Interface Innovator
6 - Interface Innovator

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.

Joanna_Parker
6 - Interface Innovator
6 - Interface Innovator

Hi guys, I've got a similar query where I need to find out how many Saturdays are in the current month. Needs to be a formula as it's going to be a rolling thing that is linked to a monthly automation.

I've gone over this thread a few times, and I cannot for the life of me figure out how you guys pieced these together to answer the original query... ðŸ¤”

Was there a final formula based on @Steve_Haysom's combination of options that solved the issue that @Malakai was having?