How do I create an automation that ignores certain calendar days?

Adorno

I created an automation in which I inserted 3 columns with dates in this way:

Column 1: date A
Column 2: date A +3 days
Column 3: date A +5 days

The problem:
I would like the automation to ignore weekends and public holidays (of the Italian calendar).

But I don't know how to do it, does anyone have a solution? 😊

James Keuning

Create a column that gives you the day number for the date using WEEKDAY. Then a checkbox column like:

=OR(WEEKDAY([Column 1]@row) = 1, WEEKDAY([Column 1]@row) = 7, WEEKDAY([Column 2]@row) = 1, WEEKDAY([Column 2]@row) = 7, WEEKDAY([Column 3]@row) = 1, WEEKDAY([Column 3]@row) = 7)

(I did not test that formula)

Set your automation to not run if this field is checked.

For the Italian holidays, create a sheet with all of your dates, and use INDEX/MATCH to check to see if a date matches, if so, exclude it. To do that you use a cross-sheet reference.

If you call that field Holiday, then your formula becomes:

=OR(WEEKDAY([Column 1]@row) = 1, WEEKDAY([Column 1]@row) = 7, WEEKDAY([Column 2]@row) = 1, WEEKDAY([Column 2]@row) = 7, WEEKDAY([Column 3]@row) = 1, WEEKDAY([Column 3]@row) = 7, Holiday@row=TRUE)