Sheet automation with a mix of relative dates and date ranges

wboyle111121

Hello,

One of our teams is using Smartsheet for team calendars, tracking things like time off, travel, working from home days, etc.  Key notification criteria is based on start and/or end dates.

Currently, their notification is setup to trigger when the start date is today. This doesn't catch when there is a date range (e.g. Time off starting 11/12 and ending 11/15 wouldn't be caught since today is 11/13). 

I've tried a few different condition criteria, but can't seem to catch all of the options in the same automation config - I'm hoping we can do it all in one.  I know we can have a condition criteria of AND or OR, but you can't mix the AND/OR statements for the same condition, making it difficult to catch all options.

I've studied up on the help documentation for how multiple conditions or condition paths work, and it seems like we would need discrete automation configs for each different option, which won't work because the team wants a single email for all dates/date ranges that hit today's date.

I think the 3 criteria we need to catch all options are:

     1 - Start Date is TODAY

     2 - Start Date is in the past AND End Date is in the Future

     3 - Start Date is in the past AND End Date is TODAY

Is there a way to fit this all into one automation?

 

A screenshot of one of my latest attempts is attached.  I've tried that same thing configured as condition paths and all on the same condition to no avail. If I could do a mix or AND/ORs on the same condition it would be much easier (I also submitted a feature request for this)

 

Thank you!

wboyle111121

I'm 99% sure that the automation conditions don't work how we want them to.  We have a solution using nested if statements to flag a row based on the criteria I mentioned above.

Corey.Hokanson

First, the simplest option (See screenshot)

 

Where Start Date is NOT in the Future (Includes today and anything before today)

and where End Date is NOT in the Past (Includes today and anything after today)

 

Second, to answer your question about mixing ANDs/ORs with conditions, the screenshot you posted won't work because all three conditions are never simultaneously true (so it will always fail on at least one of them). 

 

wboyle111121

Thank you! Option 1 is perfect.

 

The 3 conditions are not meant to be simultaneously true - but I'm glad to see there are multiple ways to achieve what I was looking for with existing functionality.