Automation: If new line wasn't submitted for the last business day, then notify

Riza Khan

I have a smartsheet that is populated by data it pulls from a form that's submitted daily. One of the columns is a date column, another is a location.

I'd like to set up an automation that emails me when a form hasn't been submitted for the previous day.

Logic: If yesterday was a business day and there's no new line with the the date of yesterday then notify me.

I can't seem to figure out how to get this to function.

M Underbrink

The conditional automation has limited logic that it can setup, so if you have to add extra logic, you may have to do so with "Helper Columns". You can create checkbox or dropdown columns that evaluate the condition of a cell with more complex formulas, and then use that new column to drive your automation criteria.

For this particular problem, you may want to use a sheet summary formula (not sure if sheet summaries can be used in automation, you'll need to check that) or an entirely different helper sheet. In your helper sheet for this particular problem you need to check for a few things,

Check that it's yesterday - TODAY(-1)

Check that yesterday is a weekday using weekday formula, where the result needs to be in the number range of 2-6 - AND(WEEKDAY(Toda(-1)]>1, WEEKDAY([DateColumnToEvaluate] < 7)

Put it all together by counting the total number of entries on your sheet that meet your criteria and checking a box if it's greater than 1 using a checkbox column

=IF(COUNTIFS([DateColumnToEvaluate],TODAY(-1), AND(WEEKDAY(Today(-1)]>1, WEEKDAY([DateColumnToEvaluate] < 7)) >1, 1, 0)

Run your automation at a certain time at the end of the day (12:01am), to see if the column is checked. If it's not checked, proceed with your workflow.

I haven't tested any of this, but hopefully this gets you moving in the right direction...

Ramzi K

@Riza Khan

Create a workflow that alerts you if the value in a "Days Since Last Entry" reference cell exceeds 1 (i.e. more than one day)

You sheet could look like this:

With the formula in the reference cell being =TODAY() - MAX(Date:Date)

The workflow will look like this:

I hope this helps.

Ramzi