从多选日期列中计算周数
答案
-
保罗新来的 ✭✭✭✭✭✭如果你只需要将持续时间乘以所选日期的数量，那么你可以使用这样的方法:
=[持续时间(小时)]@row * COUNTM([完成日期]@row)
thinkspi.com
-
伊戈尔 ✭嗨,保罗,
谢谢你！这样我们就能计算出总工时了。
但我们还有另一个问题:我想计算每个参与者每周累积了多少小时。我需要将每个日期转换为一周，然后合计每个人的小时数。
如何从“多选”列中提取单个日期?
然后，我可以算出每个人每周的工作时间。
-
保罗新来的 ✭✭✭✭✭✭
提取日期可能有点麻烦。首先，您需要一个日期类型列，用于为单个条目选择尽可能多的日期。因此，如果某人选择的日期不超过10个，则需要另外10个列。然后，我们需要组合一个相当复杂的公式来解析选择，然后将它们转换为实际的日期。这是可能的，但这是一个相当大的工作量。如果你想走这条路，我很乐意帮助你计算公式。
解决这两个问题最简单的方法…让用户为每个日期/条目填写一个表单，而不是将一堆看起来像日期的文本值集中在一起，然后试图解析它们。
thinkspi.com
-
伊戈尔 ✭
嗨,保罗,
我们决定允许用户在Sheet中输入多个日期，而不是在Form中，这是出于两个客户服务原因:
- 用户可以每周做一次，并在一行上报告多个日常活动，而不是在表单上填写七次。
- 用户可以看到有多少人在做这件事，这将激励他们也这么做。
我喜欢创建七列的想法。每天一个。然后从多选中提取每一天到每一列中。
如果有人输入超过7天，我将手动将其分成两个记录，每个记录有7个或更少的条目。
我们如何写出一个公式来将天数提取到单独的列中?
蒂雅
帮助文章资源欧宝体育app官方888
类别
Try this<\/p>
=COUNTIFS(ryg:ryg, \"Red\", [New Target Finish]:[New Target Finish], @cell<>\"//www.santa-greenland.com/community/discussion/91474/\")<\/p>
=COUNTIFS([New Target Finish]:[New Target Finish], @cell = \"//www.santa-greenland.com/community/discussion/91474/\", [Task Name]:[Task Name], @cell <> \"//www.santa-greenland.com/community/discussion/91474/\")<\/p>
For your empty Target Finish count I added your primary column to the formula as a filter - otherwise the formula would find the empty cells all the way down the sheet regardless if you were using the row or not.<\/p>
Do these work for you?<\/p>
Kelly<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-30 16:28:14","updateUser":{"userID":119439,"name":"Lisa Matthews","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20Matthews","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!GlFVn_Nz2Xw!wlDUP2yDueY!ag-sVUwuD_X","dateLastActive":"2022-10-30T16:28:16+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":97044,"type":"question","name":"Help combining: IF INDEX MATCH","excerpt":"Hi, Please help me with my formula, I'm trying to GET a Contact Column, based on matching criteria, (The columns referenced are from a separate sheet.) I tried using: =IF({Sheet 1 Range Status Column}, \"Approved\", INDEX({Sheet 1 Range Contact Column}, MATCH(HAS(@cell, Reason@row), {Sheet 1 Range Reason Column}, 0))) It's…","categoryID":322,"dateInserted":"2022-10-26T22:04:44+00:00","dateUpdated":null,"dateLastComment":"2022-10-30T20:05:39+00:00","insertUserID":123243,"insertUser":{"userID":123243,"name":"A Rose","title":"","url":"https:\/\/community.smartsheet.com\/profile\/A%20Rose","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T20:05:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":123243,"lastUser":{"userID":123243,"name":"A Rose","title":"","url":"https:\/\/community.smartsheet.com\/profile\/A%20Rose","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T20:05:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":45,"score":null,"hot":3333985823,"url":"https:\/\/community.smartsheet.com\/discussion\/97044\/help-combining-if-index-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97044\/help-combining-if-index-match","format":"Rich","tagIDs":[254,319],"lastPost":{"discussionID":97044,"commentID":349252,"name":"Re: Help combining: IF INDEX MATCH","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/349252#Comment_349252","dateInserted":"2022-10-30T20:05:39+00:00","insertUserID":123243,"insertUser":{"userID":123243,"name":"A Rose","title":"","url":"https:\/\/community.smartsheet.com\/profile\/A%20Rose","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T20:05:45+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":"2022-10-30T20:05:50+00:00","dateAnswered":"2022-10-28T08:30:26+00:00","acceptedAnswers":[{"commentID":349088,"body":"
Here's an example of a source sheet:<\/p>
![\"Screenshot](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/MV7Z0Q2R2KWP\/screenshot-2022-10-28-at-09-23-22.png\")
<\/img><\/a>\n <\/div>\n<\/div>\nThen the formula output:<\/p>
![\"Screenshot](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/UZ3PNM76RQWJ\/screenshot-2022-10-28-at-09-25-20.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>
There isn't a direct way to then convert the multi-select options back into Contacts using a formula. <\/p>
Yes, you could use a VLOOKUP or INDEX(MATCH. In this instance you would need to have a reference sheet with all possible contact combinations<\/em>. For example, a multi-select column with each possible combination on the left, and a contact column with the Contact equivalent. <\/p>
In my image above, I have 3 contacts. This means the reference grid would have 7 rows\/options:<\/p>
Genevieve \/ Krissia \/ Mirka \/ Genevieve & Krissa \/ Genevieve & Mirka \/ Krissia & Mirka \/ Genevieve, Krissia, and Mirka<\/p>
Let me know if I can help clarify anything further! <\/p>
Cheers,<\/p>
Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-30 20:05:50","updateUser":{"userID":123243,"name":"A Rose","title":"","url":"https:\/\/community.smartsheet.com\/profile\/A%20Rose","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-30T20:05:45+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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":319,"urlcode":"functionality","name":"functionality"}]},{"discussionID":97129,"type":"question","name":"Getting NETWORKDAYS to Work with Due Date Comparisons","excerpt":"Hello everyone, I am using Smartsheet to capture a bunch of data for vendor project tracking. I'm adding a few helper columns to compare some dates so I can evaluate performance and trends. However, I seem to be having trouble with the data for one of these helper columns so hoping to share what I've got to see how I've…","categoryID":322,"dateInserted":"2022-10-28T17:36:58+00:00","dateUpdated":null,"dateLastComment":"2022-10-28T18:29:57+00:00","insertUserID":154338,"insertUser":{"userID":154338,"name":"RyanFMurphy","url":"https:\/\/community.smartsheet.com\/profile\/RyanFMurphy","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!x1jQ8UirIZQ!W7ldX3b-ZDo!YO6bd86eyZo","dateLastActive":"2022-10-28T18:37:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":67611,"lastUser":{"userID":67611,"name":"Michael Culley","url":"https:\/\/community.smartsheet.com\/profile\/Michael%20Culley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9BX019BZTFOW\/n0CCMUI3GCT23.jpg","dateLastActive":"2022-10-28T18:29:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":29,"score":null,"hot":3333962815,"url":"https:\/\/community.smartsheet.com\/discussion\/97129\/getting-networkdays-to-work-with-due-date-comparisons","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97129\/getting-networkdays-to-work-with-due-date-comparisons","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":97129,"commentID":349192,"name":"Re: Getting NETWORKDAYS to Work with Due Date Comparisons","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/349192#Comment_349192","dateInserted":"2022-10-28T18:29:57+00:00","insertUserID":67611,"insertUser":{"userID":67611,"name":"Michael Culley","url":"https:\/\/community.smartsheet.com\/profile\/Michael%20Culley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9BX019BZTFOW\/n0CCMUI3GCT23.jpg","dateLastActive":"2022-10-28T18:29:31+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":"2022-10-28T18:28:49+00:00","dateAnswered":"2022-10-28T18:28:44+00:00","acceptedAnswers":[{"commentID":349191,"body":"
