Is there a way to timestamp using a formula?

AimeeRH · 09/26/23

Apologies if this has been asked previously, I am wondering if there is a formula (or any other method) that can be used that can add a timestamp into a cell for when the workflow was triggered.

For example, I have a workflow that sends an email to person 1 and i would like the date and time in a cell that this was sent to person 1

Thank you in advance! 😊

Paul Newcome · 09/28/23

@Paul Rosas Try specifying an exact match by dropping a zero into the third portion of the MATCH function.

MATCH([Column Name]@row, {Range}, 0)

Paul Newcome · 09/26/23

You won't be able to use a formula to timestamp a workflow, but you can leverage the modified (date) type column to timestamp based on the same trigger as your workflow.

You would use the method below to capture the time stamp and then an INDEX/MATCH to pull the timestamp back onto the working sheet.

Date/Time Stamp Option

Paul Newcome

Nov 21, 2019

While trying to figure something else out, I stumbled across a new option for having specific actions date/time stamped using the stem generated Modified (date) type column and the new Copy Row automation.

.

DISCLAIMER:

This does not scale very well as is. If you have a lot of rows you are wanting to track a lot of changes on, it can get very cumbersome very quickly.

My personal use for this solution is tracking a one-time change on each row (think "Completed" box gets checked).

.

Here's how it works...

I have the system generated Modified (date) column. I have another set of columns that separates the date from the time for further calculations, but you could even use a single extra column that simply duplicates the Modified column.

Set up the automation to trigger according to what you want to capture the date/time stamp for.

When a row is copied, only the data is copied. Not the formula. So whatever data is in the extra column that is formula driven to duplicate the Modified column will be pulled, but the formula to duplicate won't. It will also pull attachments and comments as well.

This effectively captures the date/time stamp for a specific action.

.

Yes. The date/time stamp is on a different sheet, but that just means that you can restrict access to the historical data.

Yes. It doesn't scale very well, but at least it is a step in the right direction and gives us an option to date/time stamp natively within SS.

Paul Rosas · 09/27/23

@Paul Newcome

Paul,

Whenever I followed your instructions here, I ran into an issue:

The column on my main page (cutting completed timestamp) that I input the INDEX/MATCH formula into to pull the timestamp over into my main page doesn't actually show it as a timestamp. The most I can get is the date (if i categorize the column as a "date" column), but I need the whole time stamp as well. I believe it's because of the type of column. Also, whenever I turn it into a "Modified Date" column, it doesn't let me input any formula.

Keep in mind that the column I have in the sheet that I'm trying to reference (the one containing the copied over rows from the automation) is a Modified Date column.

Please help!

Paul Newcome · 09/27/23

@Paul Rosas You are going to need to modify the INDEX/MATCH to pull in as a text string and have it placed in a text/number type column.

=INDEX(........, MATCH(.......)) + ""

Adding "plus quote quote" to the end of the formula will convert it into a text string and should pull over everything as needed.

Paul Rosas · 09/28/23

@Paul Newcome

Thank you for that!

I'm now experiencing another issue. Although my sheet that contains my copied rows from the automation trigger only has three rows that I've checked off, my main sheet formula (cutting completion timestamp column on bottom photo) returns a bunch of times, even though there is no matches. As you can see, my reference sheet only has 3 SO#'s to match to, even though my main sheet is pulling all kinds. And i've triple checked to make sure there aren't copied rows at the bottom that I was unaware of, also that each SO# on my sheets are unique to those specific rows.

I've also attached my formula. Please help.