Timed Approval Process

Paul Newcome

Here are the updates that I have made to my sheet. I will check back on them around this time tomorrow to see if it has improved anything.

[Last Update]1:

=MAX(COLLECT(Modified:Modified, Primary:Primary, @cell <> "")) + ""

[Current Hour]1:

=RIGHT([Last Update]@row, LEN([Last Update]@row) - FIND(" ", [Last Update]@row))

Rabi Baig

I just checked my sheet and mine did the same thing. It was not refreshed until I open the zzzTodayzzz sheet. It was about 3 hours behind in the main sheet

Paul Newcome

Ok. Lets try this then. Instead of trying to extract the hour, lets just run the MATCH function based on the most recently updated row. It will output a row number, but because of how we have the sheet set up, we will end up outputting a number that represents the hour already in 24 hour format.

You will also want to go back to your data sheet and adjust it accordingly since we are no longer pulling a text value out of the TODAY sheet.

Make sure the [Last Updated]1 cell is that last formula I posted. This will ensure that we can see at a glance which one was last updated just as a kind of check.

Then in the [Current Hour]1 cell enter this:

=MATCH(MAX(COLLECT(Modified:Modified, Primary:Primary, @cell <> "")), Modified:Modified, 0) - 2

If this doesn't work then I do have a couple of other things that I can try, but hopefully this does the trick. I'll keep an eye on it as well and let you know if I run into any issues.

Rabi Baig

I have adjusted the column formula in Today sheet. What adjustment do I need in my data sheet? This is what the formula looks like 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, 0), IF(FIND("P", {zzzTodayzzz Range 1}) = 0, -12))

I tried to fix on my own but i have zero knowledge on how this formula is working.

Rabi Baig

I actually remove the formula from my data sheet and created a cell link to get the value from the Today sheet's "Current Hour". I don't know if I did this right or not but now I am facing another issue which makes the cell link to the first cell only rather than for the entire column in my data sheet.

Paul Newcome

You can still use a formula to pull that one cell directly. the easiest way to do this would be to go ahead and type out

=SUM(

Then create a cross sheet reference to that singe cell.

Leave the cell that has the SUM formula in it, then go back to it and remove the SUM function leaving only the cross sheet reference.

={Cross Sheet Reference To Single Cell}

You can then set this to be a column formula.