Sheet automation with a mix of relative dates and date ranges
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)