修改人及修改日期

比尔Comeau
比尔Comeau ✭
在公式和函数

你好，我正在寻找最好的方式添加日期修改和修改由一个仪表板总结一页。这与用于行的Modified函数不同。我正在寻找一个函数收集这些字段，如果一个表上的任何东西被修改。

感谢帮助!

最佳答案

  • 吉纳维芙P。
    吉纳维芙P。 员工管理
    ✓回答

    嗨@Bill Comeau

    我的方法是使用修改日期和修改日期系统列在表。

    然后我将有两个文本/数字表汇总字段(如果没有表单摘要，可以有两个辅助列)返回马克斯日期从Modified Date列中显示最近的日期，然后第二个字段将使用这个MAX日期来查找该行的Modified By。明白了吗?

    屏幕截图21-02-05上午11.59.27。png


    下面是我在Sheet Modified单元格中的公式:

    =MAX([修改(日期)]:[修改(日期)])+ ""

    我在末尾添加了引号，以便将修改日期转换为文本，然后包含时间戳。您需要将其放在文本/数字字段中。


    然后我们可以使用它在相关的行中查找用户。我用一个加入（收集公式，如果两个用户同时修改它，它将返回两封邮件:

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


    您会再次注意到，我正在搜索Modified (Date)列，以查找值为@cell +”“(日期的文本版本)，并将其匹配到第一个公式的值，在上面的表单摘要字段:@cell + "" =[表单修改]#)

    您可以使用表摘要字段作为度量小部件在你的仪表板。这对你有用吗?

    干杯!

    吉纳维芙

答案

帮助文章资源欧宝体育app官方888

在Smartsheet中创建和编辑公式

交叉表引用的公式组合

内容功能列表

类别

@keesuri25<\/a> <\/p>

Your formula is working for me:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

What type of column is YTD on your sheet? It needs to be a Date type column.<\/p>"},{"commentID":346398,"body":"

It looks like you have a minor syntax issue. Whenever you reference a column name that has a space, number, and\/or special character in it, you have to wrap the column name in square brackets whereas you wrapped the entire reference in square brackets. Try this:<\/p>

=COUNTIFS([Engagement Phase]:[Engagement Phase], \"COMPLETED\", [PoC End date]<\/strong>:[<\/strong>PoC End date], YEAR(@cell) = 2022)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-07 00:22:30","updateUser":{"userID":153291,"name":"keesuri25","url":"https:\/\/community.smartsheet.com\/profile\/keesuri25","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-07T00:19:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":96311,"type":"question","name":"INDEX \/ MATCH Formula Question","excerpt":"Hello, I am missing something with executing this formula. This is my first time trying to apply to my sheet. I can only successfully return a #Unparseable error. 😥 Would appreciate any guidance you can provide. Formula: =INDEX({CI Weight}), MATCH([Task Name]@row, {Project Name}, 0) Goal: Pull in ranking (CI Weight) from…","categoryID":322,"dateInserted":"2022-10-06T17:01:23+00:00","dateUpdated":null,"dateLastComment":"2022-10-06T23:25:11+00:00","insertUserID":134324,"insertUser":{"userID":134324,"name":"Marcela Hernandez","url":"https:\/\/community.smartsheet.com\/profile\/Marcela%20Hernandez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-07T03:32:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":134324,"lastUser":{"userID":134324,"name":"Marcela Hernandez","url":"https:\/\/community.smartsheet.com\/profile\/Marcela%20Hernandez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-07T03:32:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":53,"score":null,"hot":3330178594,"url":"https:\/\/community.smartsheet.com\/discussion\/96311\/index-match-formula-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96311\/index-match-formula-question","format":"Rich","lastPost":{"discussionID":96311,"commentID":346402,"name":"Re: INDEX \/ MATCH Formula Question","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/346402#Comment_346402","dateInserted":"2022-10-06T23:25:11+00:00","insertUserID":134324,"insertUser":{"userID":134324,"name":"Marcela Hernandez","url":"https:\/\/community.smartsheet.com\/profile\/Marcela%20Hernandez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-07T03:32:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/LKAW8HFTGVXC\/index-match-question.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"INDEX MATCH question.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-06T20:52:07+00:00","dateAnswered":"2022-10-06T20:47:41+00:00","acceptedAnswers":[{"commentID":346374,"body":"

Remove the ) after CI WEIGHT<\/p>"},{"commentID":346375,"body":"

My hero!! Muchas gracias, @Michael Culley<\/a> ! 🤩<\/span>🤩<\/span><\/p>"},{"commentID":346400,"body":"

If you want it to only apply to parent rows then it would look more like this:<\/p>

=IF(COUNT(CHILDREN([Task Name]@row)) <> 0, <\/strong>INDEX({CI Weight}, MATCH([Task Name]@row, {Project Name}, 0)))<\/strong><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-06 20:52:07","updateUser":{"userID":134324,"name":"Marcela Hernandez","url":"https:\/\/community.smartsheet.com\/profile\/Marcela%20Hernandez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-07T03:32:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":96312,"type":"question","name":"Can I use the Countif function to count the amount of times one column is greater than another?","excerpt":"For example, in my Smartsheet I have two columns that I would like to compare data. \"Sprinkler Head Count (Estimated)\" versus \"Sprinkler Head Count (Actual).\" I would like to count how many times in my sheet where the Actual head count is greater than the Estimated head count. Any ideas how we can achieve this? Thanks in…","categoryID":322,"dateInserted":"2022-10-06T17:10:54+00:00","dateUpdated":null,"dateLastComment":"2022-10-06T17:16:52+00:00","insertUserID":151299,"insertUser":{"userID":151299,"name":"lcain","url":"https:\/\/community.smartsheet.com\/profile\/lcain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-06T20:25:53+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":145151,"lastUser":{"userID":145151,"name":"Mike TV","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20TV","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AItbvmniEdReOVTwaHHrhgrOiwux3krYT43KIqrvf5FW=s96-c","dateLastActive":"2022-10-07T00:06:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3330154666,"url":"https:\/\/community.smartsheet.com\/discussion\/96312\/can-i-use-the-countif-function-to-count-the-amount-of-times-one-column-is-greater-than-another","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96312\/can-i-use-the-countif-function-to-count-the-amount-of-times-one-column-is-greater-than-another","format":"Rich","lastPost":{"discussionID":96312,"commentID":346312,"name":"Re: Can I use the Countif function to count the amount of times one column is greater than another?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/346312#Comment_346312","dateInserted":"2022-10-06T17:16:52+00:00","insertUserID":145151,"insertUser":{"userID":145151,"name":"Mike TV","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20TV","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AItbvmniEdReOVTwaHHrhgrOiwux3krYT43KIqrvf5FW=s96-c","dateLastActive":"2022-10-07T00:06:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/XULU5EB0Y7VE\/estimate-v-actual.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Estimate v Actual.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-06T17:15:39+00:00","dateAnswered":"2022-10-06T17:14:49+00:00","acceptedAnswers":[{"commentID":346310,"body":"

create a checkbox column with this formula: <\/p>

=if(Sprinkler Head Count (Actual) >Sprinkler Head Count (Estimated),1,0)<\/p>

Then you can do a countif statement counting the checkboxes: =countif([checkbox column]:[checkbox column],1)<\/p>"},{"commentID":346312,"body":"

@lcain<\/a> <\/p>

I'm not sure if it can be done with a single formula. Here's what I would do.<\/p>

Create a helper column that's a checkbox column called something like \"Sprinkler (helper)\". Write this formula into it:<\/p>

=IF([Sprinkler Head Count (Actual)]@row>[Sprinkler Head Count (Estimated)]@row, 1)<\/p>

Then hide that column since you don't need to look at it on your sheet. Then in the column you want the count use this formula:<\/p>

=COUNTIF([Sprinkler (helper)]:[Sprinkler (helper)], 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-06 17:15:39","updateUser":{"userID":151299,"name":"lcain","url":"https:\/\/community.smartsheet.com\/profile\/lcain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-06T20:25:53+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">