Automation reminders: weekends and holidays, send a day before

Stephanie Swenson

Hi,

I have seen other Q/As about how to add a helper column with a checkbox and formula, to run a reminder if it is a workday, so as to avoid sending on weekends and holidays.

But if it is not a workday, how to I get a reminder to still run, but on the day after or day before the non workday, so that the automation goes out either way?

Thanks

Stephanie

KPH

Hi

You would need a helper column with a formula to determine the date to run. There are a few ways to do that. I'll start with the two simplest methods.

1) When you calculate the reminder date do this using the WORKDAY function. So, if you need to send a reminder a week after something happens, take that date and add 5 workdays to it. If this is a weekend or holiday, it will move to the next working day.

This formula looks at the date in the Date column and adds 6 working days.

=WORKDAY(Date@row, 6)

You can add other holidays to exclude by adding them to the formula, referencing a cell, or referencing a column,..

=WORKDAY(Date@row, 6, DATE(2024, 2, 19))

2) For weekends (but not holidays) you can use the WEEKDAY function to check if the day is a Saturday or Sunday and add or subtract days as appropriate.

This formula looks at the day of the week in the Date column (you can replace this reference with a formula). If the day of the week is a Sunday it subtracts 2, and if the day of the week is a Saturday it subjects 1, otherwise it returns the date in the Date column. You could change -2 to be +1 to return Monday for Sunday.

=IF(WEEKDAY(Date@row) = 1, Date@row - 2, IF(WEEKDAY(Date@row) = 7, Date@row - 1, Date@row))