Stale time/date

Marcus Lintault

Hi,

I am trying to setup a "Stale" notification for a help ticketing system.  When a support ticket (row) ticket has not been modified when compared to the current time and date in over an X amount of hours, then send a notification.  I can compare two different date columns like this:

=Modified1 - [Created Date]1

But if I do this:

=today()- [Created Date]1

It does not calculate the time and uses only the number of days.  So if it is a new ticket, and it sits there more then 2 hours without any attention, I want to have a notification sent.  Decimal days are ok for the hours (an hour is 0.041666) but I cannot figure out a way to do this because there is no TIME function and is not a property of any of the day or date functions (at least that I can decipher).

Any ideas?

Thanks,

Marcus

Andrée Starå

Hi Marcus,

I might have a solution, but I have to try it out first. I'll get back to you in about 10 hours. 

Best,

Andrée Starå - Workflow Consultant @ Get Done

Marcus Lintault

Some examples of how it works now:

=Modified1 - [Created Date]1

I get an answer like this: 1.23678   ( this = 1 day + 5.68 hours)

But if I do this:

=TODAY() - Modified1 

I get this:  1.000

Thanks,

Marcus

Marcus Lintault

Thanks Andrée!

Andrée Starå

Happy to help!

Unfortunately, my solution didn't work, but I'll get back to you if I solve it. 

 

Paul Newcome

I wasn't able to figure anything out either. Not being able to calculate using CURRENT time really puts a damper on this one. I'm going to keep working on it as well though.

J. Craig Williams

Regardless of which way you slice it, there is nothing in Smartsheet that will create a time stamp, other than the [Created] and [Modified] columns.

Option 1: Use a third party tool like Zapier to force an update on a specific row, hourly.

Specifically for Zapier, that is at most 744 (24 *31) successful Zaps per month, which is large chunk of the allotted successful Zaps for the basic account. But not all of them.

My post here describes doing this once per day and can be used as a guide for doing it more often:

http://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/

Then compare the time stamp for each row of interest as it changes.

Option 2: Write code using the API. I have a tool that already does the Refresh Today functionality and can be modified to do it hourly. I'm still working out the front-end but the back-end runs from my computer hourly using Task Scheduler.

Craig

Marcus Lintault

Craig,

Thank you for the suggestions.  I shall try them!

 

Regards,

Marcus