Timed Approval Process
I am trying to set up an Approval workflow where if the first approver didn't respond within the first 48 hours, the form needs to go to the second approver. Is there a way to do that?
Answers
-
I would suggest inserting another date column that has a formula in it to produce a date two days after the original submission then setup another approval request based on this column and setting the condition to if the approval column is still equal to "Submitted".
-
Thank you Paul.
I am also looking to do the same scenario but this time I am only waiting 15 minutes for the first approver, 60 minutes for the second and if the two approvers didn't respond then it will go the final one.
-
@Rabi Baig The smallest increment that can be automated within Smartsheet is 60 - 75 minutes. Technically it is 60 minutes, but the automations could take up to 15 additional minutes to run (I have never seen this setup run more than 9 minutes after the hour).
-
Thanks for the help Paul!!!
-
I have a timestamp field and the only option it is giving me in the workflow condition is for a date but not time. I am trying to set up a workflow where the timestamp is more than an hour and
'1st approver Appoval' column is empty then run 2nd approval process.
In other words, I am trying to do the 60 minutes workflow like you told me but couldn't figure the logic.
-
It is actually going to require a bit of extra setup. Let's start with setting up how to track the time in hourly increments. Create a new sheet and set it up as below.
TIP: I named mine as "zzzTODAYzzz" for a few different reasons. It is the only sheet that starts with "zzz" which serves 3 purposes.
- It is the only sheet that starts with that, so when I search for it whether it be to open it or link to it from another sheet, I can very quickly locate it
- When everything is sorted alphabetically it is out of the way at the bottom of the list
- It reminds me that this is a "sleeper" sheet meaning it is one that I should never have to touch again once set up (which I haven't other than to take screenshots since I first created it back in December of 2020).
Then I threw in "TODAY" so that I know what exactly is in that sheet and added the last three z's because of OCD. Haha.
Explanations of each column is below the screenshot.
[Today] (date type column):
In row 1 I have
=TODAY()
Rest of column blank
[Current Hour] (text/number type column):
In row 1 I have
=SUBSTITUTE(MAX(Modified:Modified), DATEONLY(MAX(Modified:Modified)) + " ", "")
Rest of column blank
[Last Update] (text/number type column) (optional):
In row 1 I have
=MAX(Modified:Modified) + ""
Rest of column blank
[Primary] (primary column or text/number type column):
Row 1 is blank
Then I have "a" through "x" going down the rows (manually entered)
[Time] (text/number type column):
Row 1 is blank
Then in each row that has a letter in [Primary], I entered an hour based on a 24 hour time
[Automation Date] (date type column):
Leave blank for now. This will be populated as the automations run
[Modified] (system generated Modified (date) type column):
Nothing needed by you in this column either
TIP: I put the [Today] column with the "=TODAY()" in the top left cell because that is where the selection defaults to when creating a cell link from another sheet. Typically I only need to automate the date each day at midnight to refresh the sheet daily, so if I can just select the sheet and click on "Create Link" then I have saved myself that one step of having to specify which cell to create the link to.
Moving right along, we now create 24 Record A Date automations. Don't worry. It isn't as painful as it sounds. Create the first one and title it "0000", set it up to run daily at 12:00am as the trigger and with the condition of [Primary] is equal to "a", and have it populate the [Automation Date] column.
Duplicate this, change the title to "0100", change trigger to 1:00am, and set condition to be [Primary] is equal to "b".
Keep duplicating until you have one for each hour.
As you can see in the screenshot, these automations usually run within the first couple of minutes after the hour, so there should be minimal delay.
Let me know when you have this sheet set up, and we can move onto the next step of getting the actual working sheet automated for you.
HINT: We link to the current hour on our zzzTODAYzzz sheet, convert it into a useable number, insert a column to convert your timestamp into a useable number based on if it is less than the "current hour" and 1st approver is blank, then set up the final automation to trigger on any row where that column becomes populated.
-
@Paul Newcome Thank you, Paul. You have been tremendously helpful. I have made this sheet. Please advise on the next steps.
-
Ok. Great. We can move back to the working sheet now. Insert a text/number column [Current Hour] and in Row 1 create a cell link to [Current Hour]1 in your zzzTODAYzzz sheet. In the cell below this enter
=VALUE(LEFT([Current Hour]1, FIND(":", [Current Hour]1) - 1)) + IF(LEFT([Current Hour]1, FIND(":", [Current Hour]1) - 1) <> "12", IF(FIND("P", [Current Hour]1) > 0, 12), -12)
Now we have converted the current hour into a 24 hour value.
Next we insert a checkbox type column. In this one we use:
=IF(AND(VALUE(LEFT([Time Stamp Column]@row, FIND(":", [Time Stamp Column]@row) - 1)) + IF(LEFT([Time Stamp Column]@row, FIND(":", [Time Stamp Column]@row) - 1) <> "12", IF(FIND("P", [Time Stamp Column]@row) > 0, 12), -12) = [Current Hour]$2 - 1, [1st Approval Column]@row = "Submitted"), 1)
This will check the box on any row where the time stamp is one hour behind and the 1st Approval column is still equal to "Submitted".
Then you set up a workflow to run at 1:00am every day on boxes that are checked. Duplicate the workflow and adjust it to 2:00am, so on and so forth until you have covered each hour of the day.
-
Hey Paul,
I have made the cell link to the other sheet. The second formula is giving me an #INVALID error. I have changed the column name to column name in my sheet in the formula. I wonder if the #INVALID error is because the timestamp column is "Auto/number(Created Date)"
This is what mine looks like:
=IF(AND(VALUE(LEFT(Timestamp@row, FIND(":", Timestamp@row) - 1)) + IF(LEFT(Timestamp@row, FIND(":", Timestamp@row) - 1) <> "12", IF(FIND("P", Timestamp@row) > 0, 12), -12) = [Current Hour]$2 - 1, [1st Approver approval]@row = "Submitted"), 1).
I have tried to put brackets around "Timestamp" but it seems to remove them every time I hit enter
-
Invalid what exactly? There are a few different errors that start with "INVALID". Knowing which one you are encountering will help with troubleshooting.
-
@Paul Newcome It is Invalid value error.
-
Can you provide a screenshot including the [Current Hour]2 cell?
-
Paul,
Here are some screenshots. Please let me know if you need anything else.
-
I missed the part where the timestamp column is the Created (date) type. we can incorporate a SUBSTITUTE function to remove the date portion.
=IF(AND(VALUE(LEFT(SUBSTITUTE(Timestamp@row, LEFT(Timestamp@row, FIND(" ", Timestamp@row)), ""), FIND(":", SUBSTITUTE(Timestamp@row, LEFT(Timestamp@row, FIND(" ", Timestamp@row)), "")) - 1)) + IF(LEFT(SUBSTITUTE(Timestamp@row, LEFT(Timestamp@row, FIND(" ", Timestamp@row)), ""), FIND(":", SUBSTITUTE(Timestamp@row, LEFT(Timestamp@row, FIND(" ", Timestamp@row)), "")) - 1) <> "12", IF(FIND("P", SUBSTITUTE(Timestamp@row, LEFT(Timestamp@row, FIND(" ", Timestamp@row)), "")) > 0, 12), -12) = [Current Hour]$2 - 1, [1st Approver approval]@row = "Submitted"), 1)
-
Paul,
The formula worked and when I tried to convert my "Check" column into a column formula it is giving me a syntax error. Below is the screenshot of the syntax error link that it told me to go
I tried putting brackets around timestamp but it takes it off every time I hit enter. Also, it is showing above that the absolute reference to the column is not supported
