Last Date Modified Column

KatrinaN

We have a column that is Last Date Modified in a sheet that has 100 plus rows in it. When I updated 1 row and save the sheet, the Last Date Modified changes for all the rows on the sheet. Has anyone else had this happen or is there a better way to set up to see just the date a particular row was updated?

I have been told it shouldn't operate this way and even deleted the column and added it back in the sheet and the same thing is happening.

Paul Newcome

Are you using the TODAY function anywhere in the sheet? Do you have any formulas anywhere?

KatrinaN

Yes, here is the formula the Smartsheet architect set up:

=IF(OR([Last Modified]@row < TODAY(-15), [Last Modified]@row = TODAY()), "Green", IF(OR([Last Modified]@row > TODAY(-15), [Last Modified]@row < TODAY(-30), "Yellow", "Red")))

Paul Newcome

The problem is with the TODAY function. It is updating on all rows on the back-end which is pushing through to the Modified (date) type column.

You are going to need to create a certain setup to accommodate capturing static data.

First you will need to make sure you have a unique identifier on each row.

Next you will create a copy of the sheet with no data in it (just need the columns).

Then you would set up a copy row automation to copy the row whenever the column(s) you are tracking change in are updated.

From there you would pull in the MAX date based on the unique ID and compare that to today using this in place of [Last Modified]@row:

DATEONLY(MAX(COLLECT({Copy Sheet Last Modified}, {Copy Sheet Unique ID}, @cell = [Unique ID]@row)))

JamesB

@KatrinaN

I have a situation similar to this. I created a Date Helper Column called Comments Last Updated, and then used a workflow to Record a date in the Comments Last Updated tracking when the Comments Cell changes. It would require multiple workflows if you need to track the changes of multiple cells in a row.

Allen4480

@Paul Newcome

Paul, sorry to bother you....

re: using the TODAY function and last modified date

I'm trying to use your suggested workaround shown below:

DATEONLY(MAX(COLLECT({Copy Sheet Last Modified}, {Copy Sheet Unique ID}, @cell = [Unique ID]@row)))

I've created a "copy" of my schedule.

I've created the automation.

I'm stuck getting the formula to work in my "master" sheet.

I created a new column in my master, called "last modified date". I'm using a column called "WBS" in the master as the unique identifier for each row.

=DATEONLY(MAX(COLLECT({COPY of NOC3.0 Project Schedule Range 2}, {COPY of NOC3.0 Project Schedule Range 3}, @cell = WBS@row)))

im getting "invalid column value"

any ideas?...or is there a newer workaround?

thanks

Allen

Paul Newcome

@Allen4480 Make sure the formula is in a Date type column.