Stale time/date

07/31/18 Edited 12/09/19

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

Comments

  • Andrée Starå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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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

  • Thanks Andrée!

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Happy to help!

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

     

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 08/01/18

    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 WilliamsJ. Craig Williams Top Contributor

    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

  • Craig,

    Thank you for the suggestions.  I shall try them!

     

    Regards,

    Marcus

     

Sign In or Register to comment.