适当的功能和持续时间
在新版本中，比例函数似乎发生了一些事情，或者它一直都是这样，只是我没有注意到(在这种情况下，我道歉)。
我使用比例函数来提供任务完成的线性投影。换句话说，我计算任务在任何一天结束时应该完成的百分比。
这个公式非常简单，由于之前的任务持续时间限制(即仅限全天)，显然不是100%准确，但如果我们现在能够使用在新的Date/ time列中维护的时间组件，这可能是可能的。不管怎样，它足够精确，很有用……
=IF([开始日期]1 <=今天()，比例(1，[开始日期]1，[结束日期]1，[开始日期]1，今天())”、“)
IF语句与我的实际目标无关，但是包含它是为了避免计算任何尚未启动的任务。
我回顾了该版本中引入的一些新功能的影响，并注意到相应的功能似乎是根据经过的时间持续时间而不是发布后的工作时间持续时间来工作的。这意味着计算是完全错误的!显然，持续时间越长，影响越小……
想象一下下面的场景;
- 任务持续时间- 3天
- 任务运行时间持续时间- 5天(周五开始，周二结束)
- 今天是星期一
上述公式的结果是，预计完工率为80%(即4/5)，而实际完工率应为66.66%(2/3)。
版主有什么评论吗?没有解决方案，我不得不放弃我的预测……
评论
-
马克·马德尔 员工嗨，雷神。
谢谢你的帖子。我们的支持和QA团队将进行调查。
最好的问候,
马克
-
特拉维斯 员工你好，托尔-感谢你的耐心，而我们调查。我与我们的QA和开发团队进行了交谈，并在当前版本和7月11日发行前的版本之间对该功能进行了一些测试。proate函数今天的行为与7月11日发布之前的行为相同。比例函数不考虑工作日，这就是为什么你的例子的结果是80%(4/5)，而不是67%(2/3)。
可以使用proate并通过在公式中使用NETWORKDAYS函数来获得您正在寻找的结果。试试这个公式:
=IF([End Date]1 < TODAY()， 1, IF([Start Date]1 <= TODAY()， ROUND(NETWORKDAYS([Start Date]1, TODAY()) / NETWORKDAYS([Start Date]1:[End Date]1)， 2)， ""))
如果你对此有任何疑问，请告诉我。
-
托尔 ✭
嗨,特拉维斯
谢谢你的帮助。这似乎奏效了。
我像上面写的那样使用它，但将它嵌套在ISBLANK IF语句中，以避免说如果缺少开始/结束日期，任务应该100%完成:-)
干杯
托尔
类别
Instead of comparing to the count of children, you would compare to the count of children that are blank or not blank.<\/p>
<\/p>
Change<\/p>
COUNT(CHILDREN())<\/p>
to<\/p>
COUNTIFS(CHILDREN(), OR(@cell <> \"\", @cell = \"\"))<\/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":106335,"type":"question","name":"Match Learner by Location in a Training Tracker for Metric Sheet","excerpt":"I am missing something, but I think it is because I am not accustom to working with dates. I am creating a metric sheet for dashboard. I have a training tracker that populates a date by a learners name & demographics (row) with columns of different courses (date columns). I didn't think the formula would be difficult , but…","categoryID":322,"dateInserted":"2023-06-12T19:39:08+00:00","dateUpdated":null,"dateLastComment":"2023-06-12T20:03:20+00:00","insertUserID":150616,"insertUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":150616,"lastUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3373200148,"url":"https:\/\/community.smartsheet.com\/discussion\/106335\/match-learner-by-location-in-a-training-tracker-for-metric-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106335\/match-learner-by-location-in-a-training-tracker-for-metric-sheet","format":"Rich","lastPost":{"discussionID":106335,"commentID":380182,"name":"Re: Match Learner by Location in a Training Tracker for Metric Sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380182#Comment_380182","dateInserted":"2023-06-12T20:03:20+00:00","insertUserID":150616,"insertUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+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-12T20:23:36+00:00","dateAnswered":"2023-06-12T19:52:03+00:00","acceptedAnswers":[{"commentID":380176,"body":"
=COUNTIFS({Learner Location Range}, [Location]@row, {Course Completion Date}, ISDATE(@cell))<\/p>
If I understand your scenario correctly, this may work.<\/p>"},{"commentID":380182,"body":"
Awesome, <\/p>
Exactly it! I was not identifying the date@cell. <\/p>
Thank you so very much!<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&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 Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
