Modified By and Date Modified
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!
Best Answer
-
Genevieve P. Employee AdminThe way I would do this is actually to use the Date Modified and Modified BySystem Columns在the sheet.
Then I would have two text/numberSheet Summary Fields(or two helper columns if you don't have Sheet Summary) to returnthe MAX datefrom 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 aJOIN(COLLECTformula 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 asMetric Widgetson your Dashboard. Will this work for you?
Cheers!
Genevieve
Answers
-
Genevieve P. Employee Admin
The way I would do this is actually to use the Date Modified and Modified BySystem Columns在the sheet.
Then I would have two text/numberSheet Summary Fields(or two helper columns if you don't have Sheet Summary) to returnthe MAX datefrom 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 aJOIN(COLLECTformula 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 asMetric Widgetson your Dashboard. Will this work for you?
Cheers!
Genevieve
-
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. Employee Admin
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.
我们可以得到国际扶轮d of the duplicate emails by adding ina DISTINCT function.
Try this:
=JOIN(截然不同的(COLLECT([Modified By]:[Modified By], [Modified (Date)]:[Modified (Date)], @cell + "" = [Sheet Modified]#)), " / ")
Let me know if this has helped!
Cheers,
Genevieve
-
Hi Genevieve. Worked perfectly! Thanks
-
Genevieve P. Employee Admin
No problem!
-
RLiriano ✭
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. Employee Admin
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 ✭
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. Employee Admin
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 ✭
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å ✭✭✭✭✭✭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:
Would that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅我的帖子(s)帮助或吗answer your question or solve your problem? Please support the Community bymarking 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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
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!
Help Article Resources
Categories
In your sheet, create a third section that adds the results of the first two. It would have the same rows, but the formula for each row would add the cells of section 1 + section 2.<\/p>
Add a helper column called ShowInReport as a Check Box and check all the rows in the third section. <\/p>
Create a report and tell it to show you only Section 3 (where the ShowInReport is true.<\/p>
I hope that makes sense.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108864,"type":"question","name":"Is there a formula to show as %","excerpt":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a decimal instead of a %. Below is the formula I am currently using. Is there a way to make it show as % =\"We are at \" + [% closed rate]@row + \"…","snippet":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a…","categoryID":322,"dateInserted":"2023-08-11T17:22:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":144360,"lastUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":36,"score":null,"hot":3383558496,"url":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","format":"Rich","lastPost":{"discussionID":108864,"commentID":390301,"name":"Re: Is there a formula to show as %","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390301#Comment_390301","dateInserted":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T19:29:56+00:00","dateAnswered":"2023-08-11T17:49:58+00:00","acceptedAnswers":[{"commentID":390269,"body":"
Try this - =\"We are at \" + [% closed rate]@row * 100 + \"% closed rate on ticket status for the month of \"+[Month]@row.<\/p>"},{"commentID":390301,"body":"
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/comment/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":59,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
