Request for help - Formula in a sheet for automatic loops (f.e. certain day in a month)

User Talk Formulas
1

Hello all,

I would like to ask you guys for help, as I’m working on a sheet to produce a loop of reminders for us like:

Task A to repeat every Monday and send out a mail which can be done with the additional actions.
Task B to repeat every first Tuesday in a month

My problem is that I have no clue how to write a formula which takes the current date and activates a trigger to send out a mail.

I already created an extra sheet where I have manually added:

Year – Month – Date – Weekday
2023 – 6 – 2 – Friday

But maybe I’m thinking to complicated. I would appreciate every hint and help.

Ahh and I had a certain case where SeaTable directly helped me with, but I don’t understand the logic behind it. (Datum = Date) The request was for a formula for the 25. of each month and when the 25. is a Saturday or Sunday show the date for the Friday before as we need it for our time management.

IF(AND(WEEKDAY(DATE(YEAR({Datum}), MONTH({Datum}), 25))>=2, WEEKDAY(DATE(YEAR({Datum}), MONTH({Datum}), 25))<=6), DATE(YEAR({Datum}), MONTH({Datum}), 25), IF(WEEKDAY(DATE(YEAR({Datum}), MONTH({Datum}), 25))>6, DATE(YEAR({Datum}), MONTH({Datum}), 25) - WEEKDAY(DATE(YEAR({Datum}), MONTH({Datum}), 25)) + 6, DATE(YEAR({Datum}), MONTH({Datum}), 25) - WEEKDAY(DATE(YEAR({Datum}), MONTH({Datum}), 25)) - 1))

But there again no clue to tell SeaTable “Please take the date and activate the trigger for it”

Anyway have a nice day everyone

Dom

2

Just use the Automation rules

3

Already doing it for the 1st of the month or a certain day of the week and certain date of the month, but in the filters is no choice to use a certain weekday. That’s why I asked for help with the formula.

Problem is that we are just working Mo - Fr and when one of those rules activates on the weekend it’s already to late. It should happens before.

4

You can use a specific weekday and time when you choose “Periodic” and “Weekly” there after you can choose a day and time.

5

Yes, I know. What I meant is when you pick monthly. I am just able to pick 1-31 instead of a day.

For example the 15. of July which is a Saturday.

How can I say to trigger the 14. instead, which is the Friday before.

So far I have 7 rules:
“1.” of the month (this rule needs the trigger from above not to happen on a weekend)
“15.” of the month (this rule needs the trigger from above not to happen on a weekend)
weekly Monday
weekly Tuesday
weekly Wednesday
weekly Thursday
weekly Friday

A little more background information to the idea. We have an internal ticket system and try to remind ourself or the team to do certain task even someone is sick or on holiday. Of course we can create reminder on outlook for ourself, but the ticket system can be seen by everyone.

6

Hey,

I just created a table, and created the columns:

Date
Year as a formular “year({Datum})”
Month as a formular “month({Datum})”
Day as a formular “Day({Datum})”
Weekday as a formular “weekday({Datum})”
Date Sub as a formular “ifs({Weekday}<6, date({Year}, {Month}, {Day}), {Weekday}=6, date({Year}, {Month}, {Day}-1), {Weekday}=7, date({Year}, {Month}, {Day}-2))”

Hopefully this helps.

Of course this does not help with national bank holidays.

7

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.