Automation to trigger monthly email for dates in a column with current month

Meredith Rhodes

I'd like to set up a conditional monthly notification to my Regulatory Staff. Here is some context.

The notification is to remind Regulatory Staff to reach out to study teams that are working on a clinical trial for which no annual continuing review is required. I have a column that either has the IRB expiration date or it says 'No CR' for No Continuing Review Required. For these studies, I'd like to use the Month in the Date of Initial Approval to send out an annual notification.

What I would like to do is send out a monthly notification on the first of each month (ie Aug 1) for ALL studies that say No CR in the IRB expiration column that were initially approved August (of any year).

How can I set this up? If I set up a helper column that just says the month of the date of approval - how do I set up conditions to only select the current month?

Thanks for any help.

Meredith

Paul Newcome

Try this in a date type column:

=DATE(YEAR(TODAY()) + IF(TODAY()> DATE(YEAR(TODAY()), MONTH([Date of Initial Approval]@row), 1), 1), MONTH([Date of Initial Approval]@row), 1)

This should output the first of whatever month is in the [Date of Initial Approval] column and will update to the next year when the current year's date is in the past.

Then you can set up your automation to run on this column.