从多选日期列中计算周数
答案
-
保罗新来的 ✭✭✭✭✭✭如果你只需要将持续时间乘以所选日期的数量，那么你可以使用这样的方法:
=[持续时间(小时)]@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":"
![\"Screenshot](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/MV7Z0Q2R2KWP\/screenshot-2022-10-28-at-09-23-22.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n![\"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
