Last Contact Date (Date Column)

Heath Sanders

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

 

J. Craig Williams

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

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

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

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

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.  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

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

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. 

• https://help.smartsheet.com/articles/1964567-system-columns
• https://www.smartsheet.com/blog/support-tip-system-columns

J. Craig Williams

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