Set up reminder for the first of the month for all inventory items needing calibration that month

I have a Smartsheet that has an inventory of equipment. I would like to be reminded when this equipment needs to be calibrated. The calibration dates are not always on the 1st, but I would like my reminder to be sent out on the 1st of the month for all items that need to be calibrated in that month.

I tried creating a new column and used the MONTH formula to change the full date into the number of the month only, which worked "11-Mar-22" in the calibration date column was listed as "5" in the new column I created. But I was not able to set up a reminder to trigger when the date is reached because it is a number and not a date.

How do I set up this reminder for the first of the month for all inventory items that need to be calibrated within that entire month?

smartsheet02MAR22.JPG

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Try something like this...

=IF(DAY(TODAY())> 1, IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)), DATE(YEAR(TODAY()), MONTH(TODAY()), 1))

On the first day of the month it will output the 1st of the month. After the first (2nd+) it will generate the first of next month. You should be able to use this column as the date based trigger for your automation.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.