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

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?