automatic reminder triggered by the system generated created (date) field

haneke.karen

I want to create a reminder to be sent automatically 5 days after the system-generated create (date), if another column is blank.

The system-generated column with the create(date) does not show in the dropdown of options for the date field.

Has anyone else run into this, and if so, any suggestions on how to get the reminder to work?

Mike Wilday

Yes, this is a common issue. The Date/time field that is system generated is not strictly a date field. To circumvent this issue you can create a helper column to reconstruct your dates. 

1. Create a new Date column
2. Insert the following formula into your new date column.
   
   =Dateonly([System Created Column Name]@row)+5

That will calculate the standard date from the System Date/time column and add 5 calendar days to it. If you need to add Working Days, try this formula: 

=WORKDAY(DATEONLY([System Created Column Name]@row), 5)

For more information on these formulas see:

• https://help.smartsheet.com/function/workday
• https://help.smartsheet.com/function/dateonly

 

L_123

You need a helper column it seems in order to do this. I would do this with a checkbox column that I would hide afterwards to check the criteria, then base a sheet level update request off of the checkbox column.

 

The formula for the checkbox column would be something like

=if(and(isblank([column reference]@row),Created@row < today(-4)),1,0)

 

Then the update request would be "If the column Checkbox changes to value 1, send an update request to XXXX"

Marty Stewart

I have an invoice approval workflow, and once an invoice is approved the workflow moves it from another sheet into my 'Approved Invoices' sheet. (where the accounts team then enter it to the ERP and mark it as processed).

I want to set up a reminder to accounts team if an item has been sitting in the approved list for more than 3 days and isn't yet processed.

If I am correct, the formula option above from @Mike Wilday won't work because the formula would have to be applied to the row manually after it has been moved to the sheet.

Anybody got any tips?

Thanks.

Mike Wilday

Have you given it a try? You might have a system column just on the new sheet. The addition of row from another sheet should still generate the system data for when the row was added. You could have the system column and the formula column already on the new sheet. Which should do the trick for you. I don't have time to test personally but it seems like a doable option.

Paul Newcome

@Marty Stewart

As long as you already have two rows containing the formula immediately above where the new row will be added, the formula will autofill.