Timed Approval Process

Paul Newcome

That is correct. The absolute reference to [Current Hour]$2 means that it cannot be applied as a column formula. We would have to adjust the [Current Hour] column data to be able to convert everything into column formulas.

Take the formula in [Current Hour]2

=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)

Then wherever you see [Current Hour]1, replace this with a cross sheet reference and have that cross sheet reference only pointing to that single cell that we created the cell link to in [Current Hour]1.

Now we can convert the updated formula into a column formula to have the current hour populated on every row within that column.

Next we go back to the formula used to check the boxes and change the [Current Hour]$2 reference to [Current Hour]@row. Now that we have replaced the absolute reference with an @row reference, we can use that as a column formula as well.

Rabi Baig

Thanks Paul.

I submitted a request at 11:48 and when the automation ran on zzzTodayzzz sheet, it changed the time to 12:08 and in the sheet I have the column "Current hour" changed the value to a zero.

This is the formula I have now

=VALUE(LEFT({zzzTodayzzz Range 1}, FIND(":", {zzzTodayzzz Range 1}) - 1)) + IF(LEFT({zzzTodayzzz Range 1}, FIND(":", {zzzTodayzzz Range 1}) - 1) <> "12", IF(FIND("P", {zzzTodayzzz Range 1}) > 0, 12), -12)

Is this what it supposed to do? Shouldn't it supposed to change it to 12 so that it can send the approval to the second approver?

Paul Newcome

I think I may have a misplaced parenthesis. Try this...

=VALUE(LEFT({zzzTodayzzz Range 1}, FIND(":", {zzzTodayzzz Range 1}) - 1)) + IF(LEFT({zzzTodayzzz Range 1}, FIND(":", {zzzTodayzzz Range 1}) - 1) <> "12", IF(FIND("P", {zzzTodayzzz Range 1}) > 0, 12, -12))

Rabi Baig

Paul,

Thank you for being helpful throughout the whole process. I noticed one thing in my sheet today that the current hour shows a negative value but as soon as I go into zzzTodayzzz sheet and refresh the sheet it changes "Current Hour" to a positive value.

Rabi Baig

@Paul Newcome Any idea on why it is doing that? Is it possible to do an auto-refresh on zzzTodayzzz sheet so it updates on its own and I don't have to open it manually to update the values in both sheets.

I really appreciate your help on this.

Paul Newcome

The record a date automation should be in the zzzTodayzzz sheet. It is still working as expected for me. If you do have the automation in that sheet, can you provide a screenshot of the trigger and condition portion of the automation?

Rabi Baig

@Paul Newcome

This is what the sheet and automation looks like in zzzTodayzzz sheet

Also, this is what the formula looks like in the main sheet for the current hour. As you can see in the below picture the number is in negative.

I have also noticed one thing in the workflows that now it gives you the option to run it hourly. Can we use that option instead of going through all this?

This dropdown gives you the option to run hourly. But I still don't know how will I trigger the flow through this. This might be a later discussion so let's focus on the "Current Hour" being negative.

Thank you

Paul Newcome

Lets try this for the formula...

=VALUE(LEFT({zzzTodayzzz Range 1}, FIND(":", {zzzTodayzzz Range 1}) - 1)) + IF(LEFT({zzzTodayzzz Range 1}, FIND(":", {zzzTodayzzz Range 1}) - 1) <> "12", IF(FIND("P", {zzzTodayzzz Range 1}) > 0, 12, 0), IF(FIND("P", {zzzTodayzzz Range 1}) = 0, -12)

I haven't tested it, but my concern with using the hourly automation on a single row is that technically the date won't change. Since the date is not changing, I am not sure that it would trigger the Modified column to update. Again... I haven't had a chance to test this yet, so feel free to test away and post the results.

Rabi Baig

Good morning @Paul Newcome

So when I opened my main smartsheet this morning, the Current Hour was not negative but it was a little behind. I am attaching some images for example

Rabi Baig

I just found out something. So the formula is working great but I think the problem here is with the cell link in Current Hour. It is not updating with the zzzTodayzzz sheet. It was showing 7 in the Current hour of the main sheet so when I open and close the zzzTodayzzz sheet and re-open the main sheet it updated the Current Hour to 10 (Current time).

I feel like everything is right, it just need something that can refresh the link somehow

Paul Newcome

That's odd. The link should be staying updated. You may want to reach out to support. Everything is working fine in my sheets.

Rabi Baig

@Paul Newcome

Good morning,

I talked to the support and they said "This behavior is expected and by design. Since in your "zzzTodayzzz" sheet, you used a Modified Date Column, this will automatically update only when you open the sheet. That's why it's not updating your "Rental Request" sheet unless you open the "zzzTodayzzz" which will update the time"

They also mentioned "At this time, Smartsheet does not yet support this functionality where the time will automatically update/generate or a formula to have this kind of result. However, I have recorded your Support ticket as feedback and on your behalf, I have also submitted an Enhancement Request to our Product team so that this functionality may be added to their lists of future developments. We highly appreciate your input!"

Is there something we can do to fix the issue?

Paul Newcome

What do you have in the [Last Update]1 cell?

Rabi Baig

Here you go

Paul Newcome

I just checked my cell history. Everything is updating as it should, but... Ever since the UI update, all of the sudden it is showing as consistently 3 hours behind even thought the timestamp in the Modified column is correct. I am going to dig into this a little bit on my end and see if I can figure out a different set of formulas we can use.