How do I create an automation that ignores certain calendar days?
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? 😊
Answers
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 505 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!