How to send out an alert when a row hasn't been updated by a certain time?

Werner Gerstacker

Maybe somebody else has run into my issue and found a creative and automatic solution.

My setup is as follows:

1) I have a sheet where I capture the project status from 10+ team members.

2) It contains a 'Modified' column.

3) The sheet contains calculations that are updated whenever the sheet is being opened (e.g % of time passed, Earned Value, Planned Value).

The use of the TODAY() function for some of the calculations seems to lead to the 'Modified' cell in each row being updated whenever I or somebody else open the sheet - even without actually/manually changing anything - so I cannot rely on the information from the 'Modified' cell to trigger an alert.

Any other suggestions to trigger an automated alert before I give in and ask my users to manually update a field or a checkbox?

P.S.:

I also tried to use a 'Modified By' column, but it seems to cause the same problem: if I just open the sheet it's my name that shows up as the 'Modified By' user.

Andrée Starå

@Werner Gerstacker

I would add it to two different columns so you'll have all the information.

Andrée Starå

Hi Werner,

Have a look at my post here.

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

That method might help/work for your use-case.

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Werner Gerstacker

Thank you for your quick response, @Andrée Starå

I was worried I'd have to take this route, i.e. doing it by copying an entry to a separate sheet and referring back to the modified values from the latest entry for each project - I'd seen solutions using the VLOOKUP/INDEX/MATCH functions for various issues but was hoping I might be able to avoid it.

On the other hand: this will also help me on my task to create an archive for the regular project reports 😮

Andrée Starå

@Werner Gerstacker

Happy to help!

Glad to hear that it can be useful for other use-cases as well!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Werner Gerstacker

@Andrée Starå - actually ended up reading back the value when the last update was archived by using this formula:

=MAX(COLLECT({Range Row Created}, {Range Project Name}, [Project Name]@row))

Given that I only get back a date from the 'Created' column, i.e. the timestamp is missing, I need to see how it works over time.

But it sees like I'm also running into the timezone issue I read about previously, i.e. a row created in Pacific Time at 8pm on 5/26 shows up as having been created on 5/27 😒

Andrée Starå

@Werner Gerstacker

Try adding +"" at the end for getting the date and time as text. Would that help?

Yes, the timezone issues can be a pain. Hopefully, Smartsheet releases a time feature soon that might help with resolving it.

Werner Gerstacker

I need to keep the result of the functions as date I assume - if I turn it into a string my trigger that's based on "Has it changed in the last 2 days" will probably not work.

But just for making the time visible it might work 👍

Andrée Starå

@Werner Gerstacker

I would add it to two different columns so you'll have all the information.

Christopher Kiener

I was hoping I could maybe get some help with this issue I am facing.