automations by hour

Dhoke

I am trying to setup an automation to let me know when something has been entered into a smartsheet for more than a set amount of hours. Ie. if someone entered a new line 1 hr ago it lets me know when an hr is up. Looks like everything is only setup in days but would like to breakdown further into hours and not sure how to do it. Using SS in a ticketing system and want to have some deadlines for 1 hr, 6 hr and 24 hrs past when someone has submitted a ticket. I have a column documenting when a ticket is submitted and it tracks the time/date so wanted to setup a reminder on the hour instead of just days.

Julie Fortney

@Dhoke Here's an idea:

This is just a very basic start to see if you like the concept.

Here's a sample sheet:

Created: auto column

Start Time: pulls just the time from the Created column. Formula: =VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) <> 12, IF(FIND("P", Created@row) > 0, 12), IF(FIND("A", Created@row) > 0, -12)) + ":" + MID(Created@row, FIND(":", Created@row) + 1, 2)

Start Time Value: converts Start Time to a value we can use for the reminder calcs. Formula: =VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(RIGHT([Start Time]@row, 2)) / 60)

1 hr Reminder: =ROUND([Start Time Value]@row + 1, 0)

6 hr Reminder: =ROUND([Start Time Value]@row + 6, 0)

24 hr Reminder: =ROUND([Start Time Value]@row + 24, 0)

Then set up workflows to run every hour during your working hours, with conditions looking at your hr Reminder columns. Here's an example of the 1hr reminder that would run at 2pm. Now, you would need to adjust these workflows to accommodate times that are outside of your working hours, and that run into the next day. For instance, your first workflow of the day will capture multiple Reminder column values (for all the hours since your last hour of the day). You'll also want to create workflows that account for hours from the day before (e.g. any Reminder value over 24).

This might take some trial and error to fully set up, but I hope this provides a good concept to get you started. Let me know if you run into questions.

Dhoke

haha. Thanks but this seems like a lot for a simple fix that I would think SS would have an answer for already - setting up an automation for every single hour in the day...Ill keep looking and playing