Automate action every first Monday of month?

1

Hi!

I’m trying to create an automation to push a button every first monday of every month (example: 2th of November, 7 of December, etc)

@Alice_Ly was super helpful and created this formula:

Formula name: “First Monday of Every Month”
Sequence(ToDate(Concatenate(Month(Today()),"-",1,"-",Year(Today()))),ToDate(Concatenate(Month(Today()),"-",1,"-",Year(Today())))+7,1).FormulaMap(CurrentValue.ToDate()).Filter(CurrentValue.WeekdayName()="Monday")

This shows the date of the first monday of the current month, so then I can create an automation: If Today = First Monday of Every Month , then “Click Button”.

I think it works, but is anyone else doing something similar?

2 Likes
2

Special thanks to @BenLee :raised_hands:

1 Like
3

Hi @Hector_Reyes :slight_smile:
I’ve seen this post that maybe can help you :slight_smile:
It’s a basic concept but it should do the job :grin:

Thanks to @Johg_Ananda :slight_smile:

3 Likes
4

As I stated in topic that @Mario linked, I really wished Coda had some more support for operations with months (both automation and formulas). As is we are forced to write some complex formulas for some pretty basic stuff (like my example of counting number of months between 2 dates or OP-s formula to run automation each month). Good thing is there are workarounds for this, since Coda does have some powerful formula tools, but there is so much room to make our life much easier for this basic stuff :slight_smile:

1 Like
5

Yes @Marko_Petrovic, dates and times still have their speed bumps. We hear you on this, it’s just not part of a project at the moment, but we will look into this at some point.

I also brought this up in a few other channels and there were a few more solutions posted…

From @Saul_Garcia and using a creative Today() - Today().Day() + 1 to find the first day of the month…

Sequence(
  Today()-Today().Day()+1,
  Today()-Today().Day()+7
).FormulaMap(
  CurrentValue.ToDate()
).Filter(
  CurrentValue.WeekdayName()="Monday"
)

And @Paul_Danyliuk reminding us all that Today().DateTimeTruncate("month") exists and will give the first day of the month as well…

Sequence(
  Today().DateTimeTruncate("month"),
  Today().DateTimeTruncate("month")+7
).FormulaMap(
  CurrentValue.ToDate()
).Filter(
  CurrentValue.WeekdayName()="Monday"
)
4 Likes
6

Here’s a more concise and performant way (one calculation instead of seven checks) to calculate first Monday of the month relative to the date.

  1. Use .DateTimeTruncate("month") to get the first day of month,
  2. Figure out how many days are there till the next Monday using this formula:
thisRow.[First day of month]
+ Days(Remainder(9 - thisRow.[First day of month].Weekday(), 7))

image
image2256×1504 315 KB

9 - Weekday because Monday is 2, so Remainder(7, 7) would calculate to zero days required to add.

6 Likes
7

@BenLee Thank you for the answer and some potential solutions. I hope date/time formulas, especially concerning months, will be improved at some point like many aspects of Coda are every month, it will make our everyday life much easier :slight_smile:

Also I have to thank @Paul_Danyliuk for his contributions, I alredy used his formula to calculacte number of months in a date range, it helped a lot :smiley:

8

If we just need to trigger an action the first Monday of the month (vs. enumerate all first-Mondays), isn’t it as simple as adding a Time-Based Rule to run on Monday and then a check if it is the first 7 days of the month:

image
image515×1176 36.5 KB

(Still learning my way around, so may be missing something obvious and happy to understand use-cases where this doesn’t work!)

10 Likes
9

hi @Tim_Burns1, thanks for the suggestion. I guess you tried it out?
It is indeed an easy set-up, done in a few minutes. My steps in line with your suggestion:

  1. put the day on today (it is Saturday for me)
  2. put the time on 11h AM (the nearest full hour in my time zone)
  3. ask for True() via IF using:
  4. Today().Day()<=15
  5. Linking it to a checkbox in a table with an easy to remember name
  6. I closed the doc in the browser (the most likely use case)
  7. and then wait and see.

Well the result is: it worked

image
image5524×1948 466 KB

Briliant!

Thanks and enjoy your day!, Christiaan

4 Likes
10

How would this formula change if you were trying to create an automation to push a button every third monday of every month?

11

Hello @Paloma_Garcia ,
I would set it up completely different.
Have the automation trigger every Monday
Set a condition: today().day() >=15 && today().day()<=21
This day range always includes the 3rd Monday of any month, therefore only triggers only on one Monday every month.
Going back to the start of this thread: the formula would be today().day() <= 7

1 Like