Setting automations based on time in a cell

Eric88

Automations can be set to run on a date found in a cell. Automations are able to be set to run at a particular time.

Is there a way to have the automation look at the time in a cell just like with dates?

This is so important as certain alerts are not only date sensitive but also time-sensitive.

Paul Newcome

@Genevieve P.'s suggestion is what I usually do. I create a helper column with a formula to output 00, 01, 02, 03, so on and so forth all the way up to 23 (making sure they are all "text" for consistent formatting) then create 24 workflows. The one for 12am has a condition of the helper column being 00. 1am's condition is 01, and on we go. It sounds like a lot, but you can create the first workflow from scratch then duplicate it, adjust the title, trigger time, and condition, save, duplicate, ............ It is a bit tedious but doesn't take too long once you get on a roll with duplicating and tweaking.

Genevieve P.

Hi @Eric88

Since Smartsheet doesn't have a Time column or Time function, there currently isn't a way for an Automation to look at text and understand what time that indicates for it to run on.

What you could do is set up multiple workflows, each that run on a different hour. Then you could use Condition Blocks in each workflow to filter what rows are eligible for the alert, such as a specific time selected in a dropdown column.

Please feel free to submit your feature request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community. You may also want to vote on the current Idea around time, here: Please let Smartsheet work with time

Cheers,

Genevieve

Paul Newcome

@Genevieve P.'s suggestion is what I usually do. I create a helper column with a formula to output 00, 01, 02, 03, so on and so forth all the way up to 23 (making sure they are all "text" for consistent formatting) then create 24 workflows. The one for 12am has a condition of the helper column being 00. 1am's condition is 01, and on we go. It sounds like a lot, but you can create the first workflow from scratch then duplicate it, adjust the title, trigger time, and condition, save, duplicate, ............ It is a bit tedious but doesn't take too long once you get on a roll with duplicating and tweaking.

Eric88

https://community.smartsheet.com/discussion/comment/385935#Comment_385935

Thank you, Paul! You're right, it did not take too long to do.

Eric88

https://community.smartsheet.com/discussion/comment/385902#Comment_385902

Thanks, Genevieve. That's a good workaround.

Seems like it should be a fairly simple thing for smartsheet to implement, at least in a rudimentary way (ie. just using hours in a dropdown rather than with minutes in a way that interacts with dates).

Paul Newcome

Happy to help. 👍️

Just showing how I personally use @Genevieve P.'s suggestion.

Novice

https://community.smartsheet.com/discussion/comment/385935#Comment_385935

I'm new to Smartsheet. What would that formula actually look like to provide that output? Thanks!

Genevieve P.

Hi @Novice

I would personally use drag-fill to do this. Type 00 in one cell and 01 in the next one:

Then you can click on the bottom-right corner to drag down the repeating pattern:

Cheers,
Genevieve

Novice

Thank you @Genevieve. I'm not sure that this makes sense to me or perhaps this solution isn't one that will work in what I'm trying to do. Maybe you can help?

I've got a sheet that has different names on each row and a column with a date and another with the scheduled times. Two hours prior to the scheduled time, I want to send reminders to various people as there is some preparation involved for each event. What I can't figure out is how to tell the reminder that the notice should go out two hours prior to the time in the column titled "MIL TIME" (24 hour time).

I understand the concept of the helper column, but I am having trouble wrapping my head around how to get it to function. Thank you!

Genevieve P.

Hi @Novice

The time in the 24 different workflows is manually configured, so if your row says 08:00 as the time, the workflow would be set for 06:00, provided that the cell has 08:00 in the row. Does that make sense?

Novice

This would work but only if I had set predictable times. Each row has a different event time, so maybe 5:00 or maybe 5:01 or 5:55… I'd have to have a workflow set for every minute of the day.

It's just so much more complicated than it should be. I really appreciate your attention to helping me figure this out.

Genevieve P.

Hi @Novice

In this instance you can adjust your helper formula column to look only at the first two values in your manual "time" column:

=LEFT([MIL TIME]@row, 2) +""

Then you can use the same suggestions above: create 24 workflows, one for each hour. This does mean it might be an alert 3 hours before the task, if the task is at the 59 minute mark.

Novice

Thank you! I didn't realize I could have it search on a partial. I can do this. :) :) :)