Using the timestamp (from system create date column) to automate task assignment

AU22

Hello -

I've been trying to test an automation workflow rule to assign tasks based on the timestamp of the request that is received but i'm stuck and could really use some advise on how to make this work.

I have a date create column and i am using this substitute & dateonly formula to extract the timestamp (being stored in a new column). I then created an automated task assignment rule, with three conditional paths -

1) If timestamp is in between 11 PM - 8 AM - assign to A

2) If timestamp is in between 8:01 AM - 3PM - assign to B

3) otherwise assign to C

Can someone advise on how i can achieve this? Many thanks!

Dr. Doug

I would like to know if Smartsheet will be incorporating any time functions as well.

Paul Newcome

You will need to convert the time into numerical data. Smartsheet currently stores it as a text string which means that it cannot be used for comparison in greater or less than.

There are a number of time based solutions in the thread below. You should be able to find something in there to help you make the conversion.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Melissa Bartness

@Paul Newcome Hi Paul, I have reviewed your formulas - thank you! I'm still having trouble with something that seems really simple, but haven't figured out how to make it work.

I have a form that when a requestor submits it, I have the Created Date column populated, which defaults to the date and time stamp. The ultimate goal is to have an automated notification to the assigned person to fulfill the request if it has not been completed within 7 days of submission (from the Created Date column).

Right now I'm using a helper column with a formula to remove the time stamp - although I haven't figured out how to have that formula replicate to new submissions yet. My thought was to then create a "Change cell value" automation to create a stand-alone (helper) date column to get my =DATE+7 column; then run my automation off that column?

I think I'm overcomplicating it but can't find another solution.

Please provide your thoughts. Thanks in advance!

Andrée Starå

Hi @Melissa Bartness

I hope you're well and safe!

I'd recommend using the DATE function and referencing the Created column and then setting up the Workflow 7 days after and with the condition to only trigger if it hasn't been completed in 7 days.

Maker sense?

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Melissa Bartness You can use something like this:

=DATEONLY(Created@row)

You can then right click on any cell containing that formula and click on "Convert to column formula" down near the bottom of the menu.

I would then set up an automation to run daily (trigger) where it has not been completed and where the date only column is 7 days in the past (conditions).

Melissa Bartness

@Paul Newcome @Andrée Starå You are both amazing! Thank you so much for your reply. These are exactly the answers I needed. I'm usually good at poking around column options but "convert to column formula" is one I overlooked!

Now that this hurdle has been cleared, I can use Andree's automation suggestion.

Appreciate you both!

Andrée Starå

@Melissa Bartness

Excellent!

You're more than welcome!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!