时间指示和DATEONLY功能
你好,
关于Smartsheet，我有以下几个问题:
- 我们利用Smartsheet在24小时内跟踪开始时间和结束时间，以计算我们完成的一些任务的持续时间。
- 我如何删除时间在“创建”，我只想有日期。(照片2)
问题1解释如下:
我注意到我们的一些一线员工会输入像“0412或0420”这样的时间，表示凌晨4:20。然而，智能表不会将这个数字注册为0412或0420，而是412或420。有没有办法确保时间显示0412或0420，而不是412或420?
最佳答案
-
杰夫瑞斯曼 ✭✭✭✭✭✭这是一种权衡。它可以是一个数字值，也可以不是，在这种情况下可以输入文本。
确保您的Date列是Date类型的列。该公式仅在列类型设置为Date时有效。
问候,
杰夫瑞斯曼，IT业务分析师及项目协调员，三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题，请将其标记为接受，以便其他用户稍后可以找到它。谢谢!
答案
-
杰夫瑞斯曼 ✭✭✭✭✭✭
问题1 -看起来在你的开始时间字段，你有“数字”验证打开。取消验证，如果输入“0420”，则保存为文本格式的值“0420”。
也就是说，如果您想对这些时间进行任何数学运算，您要么需要将它们保存为数字，要么使用任何其他公式中的VALUE函数动态地将它们转换为数值。
问题2:不能只显示日期部分的系统列“Created”。你需要使用一个辅助日期类型的列来实现这一点，使用DATEONLY函数:
= DATEONLY ((电子邮件保护)）
问候,
杰夫瑞斯曼，IT业务分析师及项目协调员，三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题，请将其标记为接受，以便其他用户稍后可以找到它。谢谢!
-
你好杰夫,
我已经删除了“仅限号码”，并将其改为“无验证”。但是，我也想确保工作人员没有输入错误的字母。我该怎么做呢?
问题2……我做过这个，但是有一个误差公式。
-
杰夫瑞斯曼 ✭✭✭✭✭✭
这是一种权衡。它可以是一个数字值，也可以不是，在这种情况下可以输入文本。
确保您的Date列是Date类型的列。该公式仅在列类型设置为Date时有效。
问候,
杰夫瑞斯曼，IT业务分析师及项目协调员，三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题，请将其标记为接受，以便其他用户稍后可以找到它。谢谢!
帮助文章参考资料欧宝体育app官方888
=COUNTIFS({Range}, @cell =<\/strong> [Job Number]@row)<\/p> Do you have any job numbers anywhere that do not have a leading zero?<\/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":106824,"type":"question","name":"pulling distinct data to another sheet","excerpt":"I have a worksheet which I am working metrics on a separate worksheet. I have a list of courses on the main sheet and they can be duplicates. What I am trying to do is pulling those distinct courses onto another worksheet and I can use count how many times they are used for, but I need to pull that distinct courses from a…","categoryID":322,"dateInserted":"2023-06-23T13:21:13+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T13:54:22+00:00","insertUserID":160445,"insertUser":{"userID":160445,"name":"topazfae","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/topazfae","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxbJ3U64IGvX8bmfaqFsly2Ax_danmhhYKsfmMgQsQ=s96-c","dateLastActive":"2023-06-23T17:46:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T17:40:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":21,"score":null,"hot":3375055535,"url":"https:\/\/community.smartsheet.com\/discussion\/106824\/pulling-distinct-data-to-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106824\/pulling-distinct-data-to-another-sheet","format":"Rich","lastPost":{"discussionID":106824,"commentID":381960,"name":"Re: pulling distinct data to another sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381960#Comment_381960","dateInserted":"2023-06-23T13:54:22+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-23T17:40:35+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-23T14:47:10+00:00","dateAnswered":"2023-06-23T13:54:22+00:00","acceptedAnswers":[{"commentID":381960,"body":" You would need a helper column on the metrics sheet (called \"Number\" in this example). You would manually enter the numbers 1 - whatever to cover the maximum number of possible unique classes. I also suggest a bit of a buffer just in case.<\/p> Then the formula to pull in the distinct list would be<\/p> =IFERROR(INDEX(DISTINCT({Course List}), Number@row), \"//www.santa-greenland.com/community/discussion/comment/\")<\/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":106782,"type":"question","name":"IF cell contains specific letter then return value","excerpt":"I'd like to return a value of 120\/208 in Service Volts for Xfmr# ending in E value of 277\/480 for Xfmr# ending in X value of 120\/240 for Xfmr# ending with no letter Thanks","categoryID":322,"dateInserted":"2023-06-22T16:38:09+00:00","dateUpdated":"2023-06-22T16:45:09+00:00","dateLastComment":"2023-06-23T17:43:27+00:00","insertUserID":158055,"insertUser":{"userID":158055,"name":"mromaire","url":"https:\/\/community.smartsheet.com\/profile\/mromaire","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T17:49:01+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":142964,"lastUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-06-23T17:57:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":44,"score":null,"hot":3374996496,"url":"https:\/\/community.smartsheet.com\/discussion\/106782\/if-cell-contains-specific-letter-then-return-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106782\/if-cell-contains-specific-letter-then-return-value","format":"Rich","lastPost":{"discussionID":106782,"commentID":382050,"name":"Re: IF cell contains specific letter then return value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382050#Comment_382050","dateInserted":"2023-06-23T17:43:27+00:00","insertUserID":142964,"insertUser":{"userID":142964,"name":"Ray Lindstrom","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ray%20Lindstrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5Y0KP6UKIBTU\/nY3615V0DPRZV.jpg","dateLastActive":"2023-06-23T17:57:55+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\/RLY9EZQ8E5Y9\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T18:08:50+00:00","dateAnswered":"2023-06-22T17:35:06+00:00","acceptedAnswers":[{"commentID":381799,"body":"
<\/p>
<\/p>
![https:\/\/us.v-cdn.net\/6031209\/uploads\/VGFVQQR38HTL\/image.png<\/a><\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/S0QZ4SV8MRDQ\/image.png<\/a><\/p>","bodyRaw":"[{\"insert\":\"Hello Jeff, \\n\\nI've removed the \\\"Number Only\\\" and made it \\\"No validation\\\". But, I also want to make sure that the staff don't input any letters incorrectly. How can I do this? \\n\\nFor question 2... I've done this but got an error formula.\\n\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VGFVQQR38HTL\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VGFVQQR38HTL\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/S0QZ4SV8MRDQ\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/S0QZ4SV8MRDQ\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2022-04-28T17:22:34+00:00","insertUser":{"userID":147175,"name":"therapy_mindset","title":"","url":"https:\/\/community.smartsheet.com\/profile\/therapy_mindset","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:39:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/327756#Comment_327756","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/327756#Comment_327756](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/VGFVQQR38HTL\/image.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/S0QZ4SV8MRDQ\/image.png<\/a><\/p>","bodyRaw":"[{\"insert\":\"Hello Jeff, \\n\\nI've removed the \\\"Number Only\\\" and made it \\\"No validation\\\". But, I also want to make sure that the staff don't input any letters incorrectly. How can I do this? \\n\\nFor question 2... I've done this but got an error formula.\\n\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VGFVQQR38HTL\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/VGFVQQR38HTL\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/S0QZ4SV8MRDQ\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/S0QZ4SV8MRDQ\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2022-04-28T17:22:34+00:00","insertUser":{"userID":147175,"name":"therapy_mindset","title":"","url":"https:\/\/community.smartsheet.com\/profile\/therapy_mindset","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:39:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/327756#Comment_327756","embedType":"quote"}">](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/S0QZ4SV8MRDQ\/image.png\"){kind=link}