Modified By and Date Modified

Bill Comeau

Hello, I am looking for the best way to add Date Modified and Modified By to a dashboard summarizing one sheet. This would be different than the Modified function for Rows. I am looking for a function to collect these fields if anything on a sheet is modified.

Appreciate the help!

Genevieve P.

Hi @Bill Comeau

The way I would do this is actually to use the Date Modified and Modified By System Columns in the sheet.

Then I would have two text/number Sheet Summary Fields (or two helper columns if you don't have Sheet Summary) to return the MAX date from the Modified Date column to show the most recent date, and then the second field would use this MAX date to find the Modified By for that row. Does that make sense?

Here's the formula I put in the Sheet Modified cell:

=MAX([Modified (Date)]:[Modified (Date)]) + ""

I added the quotes at the end to turn the Modified Date into Text, which then includes the timestamp. You'll want to put this in a text/number field.

Then we can use that to find the user in the row associated. I'm using a JOIN(COLLECT formula in case two users modified it at the same minute, which would then return two emails:

=JOIN(COLLECT([Modified By]:[Modified By], [Modified (Date)]:[Modified (Date)], @cell + "" = [Sheet Modified]#), " / ")

Again, you'll notice that I'm searching the Modified (Date) column for a value that is @cell + "" (the text version of the date), and matching that to the value from the first formula, in the Sheet Summary field above: @cell + "" = [Sheet Modified]#)

You can use Sheet Summary fields as Metric Widgets on your Dashboard. Will this work for you?

Cheers!

Genevieve

Genevieve P.

Hi @Bill Comeau

The way I would do this is actually to use the Date Modified and Modified By System Columns in the sheet.

Then I would have two text/number Sheet Summary Fields (or two helper columns if you don't have Sheet Summary) to return the MAX date from the Modified Date column to show the most recent date, and then the second field would use this MAX date to find the Modified By for that row. Does that make sense?

Here's the formula I put in the Sheet Modified cell:

=MAX([Modified (Date)]:[Modified (Date)]) + ""

I added the quotes at the end to turn the Modified Date into Text, which then includes the timestamp. You'll want to put this in a text/number field.

Then we can use that to find the user in the row associated. I'm using a JOIN(COLLECT formula in case two users modified it at the same minute, which would then return two emails:

=JOIN(COLLECT([Modified By]:[Modified By], [Modified (Date)]:[Modified (Date)], @cell + "" = [Sheet Modified]#), " / ")

Again, you'll notice that I'm searching the Modified (Date) column for a value that is @cell + "" (the text version of the date), and matching that to the value from the first formula, in the Sheet Summary field above: @cell + "" = [Sheet Modified]#)

You can use Sheet Summary fields as Metric Widgets on your Dashboard. Will this work for you?

Cheers!

Genevieve

Joseph Lynch

Hi Genevieve,

This is a great solution. However, my Second Sheet Summary formula (Join collect) is giving me every user who has modified a cell, instead of just one user. Do you know why this might be?

Genevieve P.

Hi @Joseph Lynch

I'm glad this works for you!

The reason you're seeing multiple names is because there are a number of rows that were all updated at the exact same time, at 4:50 PM on June 8th.

We can get rid of the duplicate emails by adding in a DISTINCT function.

Try this:

=JOIN(DISTINCT(COLLECT([Modified By]:[Modified By], [Modified (Date)]:[Modified (Date)], @cell + "" = [Sheet Modified]#)), " / ")

Let me know if this has helped!

Cheers,

Genevieve

Joseph Lynch

Hi Genevieve. Worked perfectly! Thanks

Genevieve P.

No problem! 🙂

RLiriano

Hi @Genevieve P. ,

I'm having an issue with the Modified Date column generated by the Smartsheet. The file we created sends a request update to the corresponding contact once a column has been checked. When the corresponding contact submits an update, we have a rule lock the row so no more updates can be submitted.

The problem we're having is that the Modified Date column is updating every time someone goes in the file, adds a row and saves. The person is not making an update to an existing row, yet, the Modified Date column updates the date and time to when the file was last saved; is this how this column is supposed to behave or am I doing something wrong?

Any help is appreciated.

Genevieve P.

Hi @RLiriano

Do you have formulas in this sheet? If you have a formula in each row that updates when the sheet is Saved (such as one that uses the TODAY() function) then it can update the "Modified" date for the entire column when a sheet is Viewed/Saved. The act of opening the sheet would cause the formula to calculate, even if the output is the same before and after it runs. Does that make sense?

RLiriano

Hi @Genevieve P. ,

Thanks so much for replying. I had not gotten back to you as I thought I would get an email notification but I didn't. I'm just checking back.

I do have several columns with formulas. Is there any way to get around this? I tried creating a column with the MIN function pointing to the Modified Date column but instead of giving me the first date when the row was modified, it gives me the latest date. For instance, the attachment below, the first date on the Modified column is 01/07/2022 and yet, using the function =min(Modified@row), returns the latest date.

Any help with this is appreciated.

Genevieve P.

Hi @RLiriano

It sounds like your formulas are running/refreshing on all rows, which then updates the Modified date for those rows.

The MIN function is only looking at the display value of the cell and cannot search through the Cell History to find the first modified date.

Is there a specific change you're looking to track? I would suggest using the Record a Date workflow to check for specific changes, then record a date that way, see: Set the current date with the Record a Date action

Cheers,

Genevieve

RLiriano

Hi @Genevieve P.,

We use this Smartsheet to request updates from other teams and we need to track the date and time when each team responded. I will try your suggestion. If there are any other suggestions you can think of, I'll appreciate it.

I thought about removing all formulas from the Smartsheet but that would mean less automation to populate cells.

Andrée Starå

Hi @RLiriano

I hope you're well and safe!

This might help!

Please have a look at my post below with a method I 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 week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

RLiriano

Hi Andrée

Appreciate the input. I will send you an email so you can share the workaround with me if possible.

Thank you!