Stale time/date

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

Find more posts tagged with

Construction

Formulas

Services

Support

IT & Operations

Email Notifications

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.