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

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

Meredith Rhodes, PhD

ClinicalTrials.gov Specialist

UW School of Medicine & Public Health

UW Clinical Trials Institute

mkrhodes@clinicaltrials.wisc.edu

Tags:

Answers

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com