Automation: If new line wasn't submitted for the last business day, then notify
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.
Answers
-
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...
-
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
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Help Article Resources
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
Check out the Formula Handbook template!