How to create a time stamped column with the date

ahrenm1

How do I create a time stamp column to auto populate the date and time of when a cell changed from blank to a value? The workflow to fill in a date does not provide the time and I cannot figure out a formula that would capture both date and time?

SSFeatures

Hi @ahrenm1, there's no direct way to do accomplish this. However there are some workarounds that might work for you, depending on how your sheet is set up.

This thread - Has a solution provided by TVang, and another provided by Andrée Starå.

The idea is to use an automation to copy the row to another sheet when the cell is modified. Then this new sheet has the "Modified" column, which will track the date and timestamp for when the row was copied into this sheet. We know that the row was copied into this sheet at the same time that the cell was modified, so this date/timestamp will be what you want.

Then you can use an INDEX/MATCH formula in your main sheet, using cross sheet references, to pull the date/timestamp back into your main sheet.

This requires your sheet to have some type of unique identifier to use to for matching.

I hope this helps!

prime_nathaniel

@ahrenm1 this is not a directly supported use case.

Your options are to use modified datetime meta field for the entire row, it is possible to parse out time but it will ofcourse change as other column values change. You could maybe get away from this by cell linking but I wouldn't recommend this at any scale.

webhooks and the api but there is a delay.

Use the API and parse through the cell history logs and pull out your timestamp
https://developers.smartsheet.com/api/smartsheet/openapi/cells/cellhistory

SSFeatures

Hi @ahrenm1, there's no direct way to do accomplish this. However there are some workarounds that might work for you, depending on how your sheet is set up.

This thread - Has a solution provided by TVang, and another provided by Andrée Starå.

The idea is to use an automation to copy the row to another sheet when the cell is modified. Then this new sheet has the "Modified" column, which will track the date and timestamp for when the row was copied into this sheet. We know that the row was copied into this sheet at the same time that the cell was modified, so this date/timestamp will be what you want.

Then you can use an INDEX/MATCH formula in your main sheet, using cross sheet references, to pull the date/timestamp back into your main sheet.

This requires your sheet to have some type of unique identifier to use to for matching.

I hope this helps!

Andrée Starå

https://community.smartsheet.com/discussion/139755/how-to-create-a-time-stamped-column-with-the-date

Hi,

I hope you're well and safe!

Please take a look at my post below, which outlines a method I have developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏