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
Answers
-
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!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives