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 Columnsin 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 Columnsin 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.
We can get rid of the duplicate emails by adding ina 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
-
嗨,吉纳维芙。体育工作rfectly! 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
The formula below will get what you need. With Smartsheet, you have to convert % to decimal values in formulas. <\/p>
=IF([Participation %]@row = 0, \"Not Participating\", IF([Participation %]@row > 0.8, \"On Track\", IF(AND([Participation %]@row < 0.81, [Participation %]@row > 0), \"Needs Improvement\", \"Blank\")))<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108795,"type":"question","name":"Error Using Max Function","excerpt":"Having an issue with the Max formula. Appreciate any advice the community can provide. This formula is giving me an Invalid Column Error: =MAX([End Date]195:[End Date]220) However, this one works fine so I don't see why there would be an invalid column error on the first formula. =NETWORKDAYS([End Date]207, [Start…","snippet":"Having an issue with the Max formula. Appreciate any advice the community can provide. This formula is giving me an Invalid Column Error: =MAX([End Date]195:[End Date]220)…","categoryID":322,"dateInserted":"2023-08-10T15:42:27+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T19:20:09+00:00","insertUserID":147716,"insertUser":{"userID":147716,"name":"Laura Krylov ASGLLC","url":"https:\/\/community.smartsheet.com\/profile\/Laura%20Krylov%20ASGLLC","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJzqP5l6RPvTnPZ_FElh_Gd3nK2OrCoGmkfTmo11=s96-c","dateLastActive":"2023-08-10T19:05:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-11T03:23:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":30,"score":null,"hot":3383379756,"url":"https:\/\/community.smartsheet.com\/discussion\/108795\/error-using-max-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108795\/error-using-max-function","format":"Rich","lastPost":{"discussionID":108795,"commentID":390113,"name":"Re: Error Using Max Function","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390113#Comment_390113","dateInserted":"2023-08-10T19:20:09+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-11T03:23:51+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-10T17:50:34+00:00","dateAnswered":"2023-08-10T15:59:34+00:00","acceptedAnswers":[{"commentID":390050,"body":"
Is the column you are inserting the formula into formatted as a date column?<\/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":108786,"type":"question","name":"Can't Save Field Logic Updates in Dynamic View","excerpt":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It asks the user to select a Category and to deter using Issues\/Miscellaneous, I have added a dropdown column in the sheet for the user to confirm…","snippet":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It…","categoryID":322,"dateInserted":"2023-08-10T14:14:23+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T16:42:12+00:00","insertUserID":158227,"insertUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T15:49:46+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-11T08:06:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3383364395,"url":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","format":"Rich","tagIDs":[443],"lastPost":{"discussionID":108786,"commentID":390063,"name":"Re: Can't Save Field Logic Updates in Dynamic View","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390063#Comment_390063","dateInserted":"2023-08-10T16:42:12+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-11T08:06:54+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/8C0H47JEM9MT\/fieldlogic.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"fieldlogic.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T15:49:43+00:00","dateAnswered":"2023-08-10T14:51:23+00:00","acceptedAnswers":[{"commentID":390035,"body":"
Hi @BristolCVN<\/a> <\/p> I hope you're well and safe!<\/p> You must add\/change the Rule #1 name.<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":[{"tagID":443,"urlcode":"dynamic-view","name":"Dynamic View"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
