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

s92ross

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

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.