Column Formula

Ron Erdeljac

I'm using this formula, =WORKDAY([End Date]@row, -2, Holidays:Holidays), to allow me to send a notification 2 days prior to an upcoming event, minus weekends and Holidays which I have define in a separate column.

I need to be able to send my notification if the event happens within the 0-2 window as well. Meaning I want my notification to to send when the event is < or = to 2 days.

Thank you in advance for your assistance!

Genevieve P.

Hi @Ron Erdeljac

The easiest way to do this would be to create 3 workflows, one for each possibility:

One would run on the date returned from your formula, one would run one day after the date from your formula, and one would run on the End Date.

However this does mean that if you have an End Date on a Tuesday, meaning the formula outputs a Friday date, then your second workflow would send on a Saturday. In this instance you actually would need a separate, second helper column with the following formula:

=WORKDAY([End Date]@row, -1, Holidays:Holidays)

Then your second alert would be based off of this second helper column. Does that make sense? This Help Article has more information on alerts based on dates.

Cheers!

Genevieve

Ron Erdeljac

Thank you Genevieve.

If I set this up as you suggested will it not be sending more than 1 notification to my participants? The purpose of trying to bridge the gap of the < 2 day notification is in case an event is moved within that window or if the event is added for the next day.

I still have concerns with the the < 2 day notification taking into account a weekend or holiday.

Meaning if I use the formula of -2 and another column formula with -1, won't they both send notifications?

thanks,

Ron

Genevieve P.

Hi @Ron Erdeljac

My apologies! Yes you are correct, this would send out 3 alerts leading up to the End Date - I misunderstood what you were looking to do.

In this instance, you may want to incorporate a Created Date System Column into your sheet. Then you can have a checkbox column house a formula that would look at the Created Date to see if it's the same as the End Date, and if it is, or if it was created one working day before, check the box. This could be a trigger for a different alert.

Rows created the same day or one day before your end date, won't trigger your previous workflow since the formula looking back 2 days would have already passed. This would require a new row to be created though, versus the end date simply being shifted. Would this work for you? If so, I can help you build this IF statement, looking at the Created Date column.

Ron Erdeljac

Hi Genevieve, I like the thought process of this approach. I already have a Created Date System Column. But here again is another challenge I see. Users are being asked to enter submissions via a Form and may enter several entries back to back such as, "Month End Close" and "Strategy Meeting", etc.

Therefore the System Create date has no relation to the, Start Date, End Date or Reminder Date being used in the workflows. Unless I'm missing your point.

Thanks!

Genevieve P.

Hi Ron,

You would only use the Created Date column to find out if the row was created/submitted from the form after 2 business days before the End Date... therefore missing your initial formula & alert.

You could say, if this row was created on the same date as what's in the End Date, check a box. That way there's an immediate alert sent out for that row. Otherwise it would be missed and no alert would be sent, since your initial formula would output a date that's in the past.

It's likely that most of your rows would be un-checked, because the End Date should be well in the future from this current date, today, when the row is created. In this case, nothing would happen, and that row would only send an alert when it reaches 2 business days before the end date due to your initial formula.

Does that make sense?

Ron Erdeljac

It does make sense, Ty I will try that!