Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Last Contact Date (Date Column)

Options

Hi Team,

I have a date column which I enter in the last contact date . Sometime I forget to enter the last date If i do an action..i.e. call, reminder, notes, e-mail etc. etc. 

Is there a formula that I can build (If I have to add a column and then hide it to make the formula/formatting etc. etc.  work no problem) that can automatically update the last date (I.e. Todays Date) in that row when I do any of the following actions Today....click on e-mail, comments or phone number cells in that row ???  Very powerful tool if this can be done...Thank You

 

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Heath,

    Referencing the [Modified] column is all or nothing. No help there.

    Smartsheet has no "trigger on change" feature or function in the GUI. No help there.

    You could set up a Zap to do this using Zapier (www.zapier.com). Zaps don't do "OR" very well, so it might be a few to setup but only count against the transaction when successful - I'd add a filter on whether the [Last Contact Date] needs to be updated before updating it to limit the transaction count (that's one way how Zapier 'gets you')

    Craig

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    FYI - Zapier is a third party option that can get pretty costly. I would submit a Product enhancement request for the functionality of adding a date stamp function. This would be EXTREMELY beneficial for a number of use-cases. And I know it something a countless number of smartsheet users have requested. Please add your vote! 

  • Heath Sanders
    Options

    Hi Mike & Craig,

    Thanks both again for giving me some insight.

    I have a thought, thinking way out side the box here.

    What would happen if I added another row ("Symbols") I had the red & white flag as my two options. When ever I changed white to red or red to white it recognized the change and look at "todays date" , so if I clicked the red flag yesterday then the "Last Contact Date" would show yesterday....My head hurts thinking about this....it might trigger the brilliance you guys have.

    Thank You Again 

    Heath

     

     

     

  • Heath Sanders
    Options

    Mike,

    Popped through that request.....very powerful tool thinking about it, as using the built in date function would eliminate a lot of human error around critical "Time Frames" 

    Thanks again 

     

     

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Hi Heath, in regard to what you are suggesting... That is not possible. Any function using Today() will always update the date to the most current day. So if yesterday you ran a function that input =Today() then yesterday it would have said yesterday's date but when the date rolled over then it would automatically change and substitute Today() for today's date. So there is no "Stamping" of date. The only semi-stamp that is possible is the last-modified system column but it always updates any time the row is updated. So, not a good fit if you're trying to stamp the date/time a certain item was checked. Any editing of the row will affect it. frown Hopefully, smartsheet will implement something soon. I could see it being incredibly useful for items like time cards, documenting the completion of certain items without requiring a person to enter a time, etc.

  • Heath Sanders
    Options

    How dose the "last-modified system" column work ? using that in the mean time could at least be a start. If you could show me how the formula works that would be very helpful 

     

    Thanks Again

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 10/17/17
    Options

    Here is an article on system columns. Basically, you insert a new column, then you can right click on the header to choose the column type. Switch it to a system column type > last modified. 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    If the LAST THING you do on a row is set % Complete to 100% (or check Done or some other known, last thing), then this formula will show the 'completed date'

    =IF([% Complete]23 = 1, MIN(TODAY(), Modified23))

    ... but since any thing that causes the Modified times tamp to change will change it, I use Zapier instead.

    Craig

This discussion has been closed.