按月安排的小时数公式
答案
-
吉纳维芙P。 员工管理
我的方法是首先找出每个月的工作天数,然后乘以“每天的小时数”。
我们可以使用MONTH函数来查找开始日期是否在您要查找的月份,如果是,则使用净工作日功能查找“开始日期”至“月底”的工作日数。
=IF(MONTH([Start Date]@row) = 3, NETWORKDAYS([Start Date]@row, Date (202,03,31)))
注意我是怎么用日期函数来确定月底。
然后我们可以用这个数字乘以(用*)每天的小时数:
=IF(MONTH([Start Date]@row) = 3, NETWORKDAYS([Start Date]@row, Date (2022, 03, 31)) + " days & " + (NETWORKDAYS([Start Date]@row, Date (2022, 03, 31))) * [Hours per Day]@row) + " Hours "
但是如果起始日期不是在那个月,我们需要检查是否结束日期就在那个月。如果是,我们可以找到从三月开始到结束日期指定的工作日数:
如果(月(结束@row) = 3, NETWORKDAYS()日期(2022、03、01)[结束日期]@row)
再加上时间:
IF(MONTH([End Date]@row) = 3, NETWORKDAYS(Date (2022, 03, 01), [End Date]@row) + " days & " + (NETWORKDAYS(Date (2022, 03, 01), [End Date]@row) * [Hours per Day]@row) + " Hours "
否则,如果两个日期都不在Month中,我们需要查看Start是否在Month中早些时候不如我们的月,而终是晚些时候而不是我们的月份,这意味着整个月份都在我们的范围内:
IF(AND([Start Date]@row < Date (2022, 03, 01), [End Date]@row > Date (2022, 03, 31)), NETWORKDAYS(Date (2022, 03, 01), Date (2022, 03, 31)) + " days & " + (NETWORKDAYS(Date (2022, 03, 01), Date (2022, 03, 31))) * [Hours per Day]@row) + " Hours "
完整的公式有三种可能性:
=如果(月(开始日期@row) = 3,工作日(开始日期@row,日期(2022年03,31))+“&”+(工作日(开始日期@row,日期(2022年03,31))*(小时/天)@row) +“小时”,如果月([结束日期]@row) = 3,工作日(日期(2022、03 01),[结束日期]@row) +“&”+(工作日(日期(2022、03 01),[结束日期]@row) *(小时/天)@row) +“小时”,如果(和((开始日期)@row <日期(2022、03 01),(结束日期)@row >日期(2022年03,31)),工作日(日期(2022、03 01),日期(2022年0331)) +“days &”+ (NETWORKDAYS(DATE(2022, 03, 01), DATE(2022, 03, 31)) * [Hours per Day]@row) +“Hours”))))
让我知道这是否适合你!
欢呼,
吉纳维芙
0 -
保罗新来的 ✭✭✭✭✭✭
除了…之外@Genevieve P。的解决方案……
由于SS没有EOM功能,我们基本上找到下个月的第一天,并从中减去1天。
=IFERROR(DATE(YEAR([Start DATE]@row), MONTH([Start DATE]@row) + 1,1), DATE(YEAR([Start DATE]@row) + 1,1,1)) - 1
thinkspi.com
1 -
保罗新来的 ✭✭✭✭✭✭
@Genevieve P。哦。这绝对不是整个公式的替代品。这就是如何严格执行Smartsheet中的EOM功能。哈哈。它实际上会使公式变长,因为它会自动计算每月的最后一天。
=IF(MONTH([Start Date]@row) = 3, NETWORKDAYS([Start Date]@row),IFERROR(DATE(YEAR([Start DATE]@row), MONTH([Start DATE]@row) + 1,1), DATE(YEAR([Start DATE]@row) + 1,1,1)) - 1) +“days &”+ (NETWORKDAYS([起始日期]@row,IFERROR(DATE(YEAR([Start DATE]@row), MONTH([Start DATE]@row) + 1,1), DATE(YEAR([Start DATE]@row) + 1,1,1)) - 1) * [Hours per Day]@row) + " Hours ", IF(MONTH([End Date]@row) = 3, NETWORKDAYS(Date (2022, 03, 01), [End Date]@row) + " days & " + (NETWORKDAYS(Date (2022, 03, 01), [End Date]@row) * [Hours per Day]@row) + " Hours ", IF(AND([Start Date]@row < Date (2022, 03, 01), [End Date]@row))IFERROR(DATE(YEAR([Start DATE]@row), MONTH([Start DATE]@row) + 1,1), DATE(YEAR([Start DATE]@row) + 1,1,1)) - 1)、工作日(日期(202,03,01)、IFERROR(DATE(YEAR([Start DATE]@row), MONTH([Start DATE]@row) + 1,1), DATE(YEAR([Start DATE]@row) + 1,1,1)) - 1) +“days &”+ (NETWORKDAYS(日期(202,03,01),IFERROR(DATE(YEAR([Start DATE]@row), MONTH([Start DATE]@row) + 1,1), DATE(YEAR([Start DATE]@row) + 1,1,1)) - 1) *[每天的小时数]@row) +“小时”)))
thinkspi.com
1 -
吉纳维芙P。 员工管理0
-
哇,你们都是巫师!它工作!
然而,我只是在寻找返回小时数的公式,这样我们就可以计算出当月所有工作的总小时数。你能提供修改后的配方吗?
我的歉意。我的例子不太清楚。我写出了天数和小时数,以显示公式如何将一个月的工作日数除以,从而得到该月安排的小时数。
0 -
吉纳维芙P。 员工管理
你每天有固定的时间吗?例如:每天8小时?如果你有,我们可以用NETWORKDAYS乘以你设定的小时数来计算那个月工作了多少小时。
在我们的例子中,我们用[小时每天]@row,但你可以直接在公式中用8代替。
我们还可以去掉“# days”这部分,使其更短。
=IF(MONTH([Start Date]@row) = 3, (NETWORKDAYS([Start Date]@row), IFERROR(Date (YEAR([Start Date]@row), MONTH([Start Date]@row) + 1,1), Date (YEAR([Start Date]@row) + 1,1,1)) - 1))* 8)+ " hours", IF(MONTH([End Date]@row) = 3, (NETWORKDAYS(Date (202,03,01), [End Date]@row))* 8)+ "小时",IF(AND([Start Date]@row < Date (2022, 03, 01), [End Date]@row > IFERROR(Date (YEAR([Start Date]@row), MONTH([Start Date]@row) + 1,1), Date (YEAR([Start Date]@row) + 1,1,1)) - 1), (NETWORKDAYS(Date (2022, 03, 01)), IFERROR(Date (YEAR([Start Date]@row), MONTH([Start Date]@row) + 1,1), Date (YEAR([Start Date]@row) + 1,1,1)) - 1) *8)+“hours”)))
这就是你要找的吗?
0 -
@Genevieve P。不完全是,我们可能每天都有很多人在做这个项目。所以生产的小时数是船员人数乘以一天8小时。下面是我的表布局与实际的列名。如果你能帮我算出每个月生产/预定的小时数,我将不胜感激。
我试图调整您之前的公式,但正如您所看到的,它返回的预订小时数比总工时要多。不知道该怎么纠正。
=IF(MONTH([Ops开始日期]@row) = 1, (NETWORKDAYS([Ops开始日期]@row, Date(2022, 1,31)) *[每天产生的小时数]@row), IF(MONTH([Complete Date]@row) = 1, (NETWORKDAYS(Date (2022, 1,1), [Complete Date]@row) *[每天产生的小时数]@row), IF(AND([Ops开始日期]@row < Date (2022, 1,1), [Complete Date]@row > Date (2022, 1,31)), (NETWORKDAYS(Date (2022, 1,1), Date(2022, 1,31))) *[每天产生的小时数]@row))))
或者我们应该用另一种方法。也许可以用该月生产的小时数除以项目的总人工小时数,得到该月生产的小时数的百分比然后乘以总人工小时数?
0 -
保罗新来的 ✭✭✭✭✭✭
您是否有针对二月、三月、四月等月份的附加专栏?
你是如何使用这些总数的?它们是否具有严格的信息,或者你是否将它们拉入图表的参数表中?
你可能需要考虑的最长时间是多少?
thinkspi.com
0 -
嗨@Paul新来的!是的,我每个月有一个专栏。从那里,我们将把每月的总数拉到生产仪表板上的一个度量表中,这样我们就可以看到我们这个月的预订情况。ob欧宝娱乐app手机下载我想,最长的持续时间是4-5个月。谢谢你的参与。感觉我们很接近了!
0 -
保罗新来的 ✭✭✭✭✭✭
嗯…好的。我觉得我们应该快找到答案了。
我们知道我们需要工作日。函数的开始日期将是每月的第一天或ops的开始日期,这取决于行的开始日期(和结束日期)。我将坚持1月份的公式,因为其他月份只需要进行一些基本的调整。
=净工作日(如果(MONTH([操作开始日期]@行)= 1,[操作开始日期]@行,如果(AND(MONTH([操作开始日期]@行)< 1,MONTH(完整日期]@行)>= 1),Date (2022, 01, 01)))),
函数的结束日期要么是完整的日期,要么是当月的最后一天。
=NETWORKDAYS(IF(MONTH([Ops开始日期]@row) = 1, [Ops开始日期]@row), IF(AND(MONTH([Ops开始日期]@row) < 1, MONTH(Complete Date]@row) >= 1), Date (2022, 01, 01))),IF(MONTH([完整日期]@行)= 1,[完整日期]@行,IF(AND(MONTH([操作开始日期]@行)< 1,MONTH(完整日期]@行)>= 1),Date (202,01,31)))))
然后,如果日期没有正确填写(因为1月份没有时间),我们使用IFERROR输出零,并乘以每天产生的小时数。
=IFERROR (NETWORKDAYS(IF(MONTH([Ops开始日期]@row) = 1, [Ops开始日期]@row, IF(AND(MONTH([Ops开始日期]@row) < 1, MONTH(Complete Date]@row) >= 1), Date (2022, 01, 01))), IF(MONTH([Complete Date]@row) = 1, [Complete Date]@row, IF(AND(MONTH([Ops开始日期]@row) >= 1), Date (2022, 01, 31))))), 0) *[每天生产的小时数]@row
这个是怎么工作的?为了对每个不同的月份进行调整,您应该能够更新我们比较月份的数字以及每个DATE函数的月份部分。
我还没有机会测试这个。如果我在你之前得到一个机会,我会回复它是为我工作还是一个不同的公式。
thinkspi.com
0 -
保罗新来的 ✭✭✭✭✭✭
看起来我遗漏了几个方括号,我添加了一个=(测试公式是针对当前月份的)。
=IFERROR(NETWORKDAYS(IF(MONTH([Ops开始日期]@row) = 4, [Ops开始日期]@row, IF(AND(MONTH([Ops开始日期]@row) < 4, MONTH([Complete Date]@row) >= 4), Date (2022, 4,1))), IF(MONTH([Complete Date]@row) = 4, [Complete Date]@row, IF(AND(MONTH([Ops开始日期]@row) <= 4, MONTH([Complete Date]@row) >= 4), Date(2022, 4,30)))), 0) *[每天产生的小时数]@row
还要记住……如果开始日期或结束日期在不同的年份,这将不起作用。如果你需要适应这一点,那么我的建议是进行一些重组。如果是这样的话,请告诉我,如果你有兴趣的话,我很乐意帮助你。
thinkspi.com
0
帮助文章参考资料欧宝体育app官方888
类别
I hope you're well and safe!<\/p>
Try something like this.<\/p>
=IF([Turnaround Time]@row = \"//www.santa-greenland.com/community/discussion/comment/326007/\", \"//www.santa-greenland.com/community/discussion/comment/326007/\", IF(AND([Lead Time]@row <> \"//www.santa-greenland.com/community/discussion/comment/326007/\", [Turnaround Time]@row > [Lead Time]@row), \"Not Met\", IF([Turnaround Time]@row <= [Lead Time]@row, \"Met\")))<\/p>
Did that work\/help? <\/p>
I hope that helps!<\/p>
Be safe, and have a fantastic weekend!<\/p>
Best,<\/p>
Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-07 11:25:24","updateUser":{"userID":137102,"name":"Laura Byrne","url":"https:\/\/community.smartsheet.com\/profile\/Laura%20Byrne","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!upsqVxYhnDs!7yMr-74eQAs!Q44B0N7isTy","dateLastActive":"2022-10-07T11:22:13+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"}]},{"discussionID":96331,"type":"question","name":"Counting matches between 2 columns","excerpt":"I am trying to see which customer numbers repeat from one week to the next. I have a column for last week, then one for this week with client #'s and this formula to look for matches. =MATCH([10.7.22]@row, [9.30.22]:[9.30.22]) From the image, clearly there are numbers in 10.7 week that are in week 9.30, but coming up as…","categoryID":322,"dateInserted":"2022-10-07T00:02:07+00:00","dateUpdated":null,"dateLastComment":"2022-10-07T15:55:02+00:00","insertUserID":134005,"insertUser":{"userID":134005,"name":"Connie Torres","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Torres","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!w6vUt79SBP4!ZYtWmWN9cnQ!oc-noNSH2sl","dateLastActive":"2022-10-07T15:12:45+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":"2022-10-07T17:44:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":27,"score":null,"hot":3330261429,"url":"https:\/\/community.smartsheet.com\/discussion\/96331\/counting-matches-between-2-columns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96331\/counting-matches-between-2-columns","format":"Rich","lastPost":{"discussionID":96331,"commentID":346481,"name":"Re: Counting matches between 2 columns","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/346481#Comment_346481","dateInserted":"2022-10-07T15:55:02+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":"2022-10-07T17:44:38+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-07T15:12:39+00:00","dateAnswered":"2022-10-07T13:48:02+00:00","acceptedAnswers":[{"commentID":346457,"body":" Try using the thrid portion of the MATCH function to specify an exact match:<\/p> =MATCH([10.7.22]@row, [9.30.22]:[9.30.22], 0<\/strong>)<\/p>"},{"commentID":346459,"body":" I believe what you want to use is a countif formula<\/p> =countif([9.30.22]:[9.30.22],[10.7.22]@row)<\/p> Or are you trying to find the row number where it matches?<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-07 15:12:40","updateUser":{"userID":134005,"name":"Connie Torres","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Torres","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!w6vUt79SBP4!ZYtWmWN9cnQ!oc-noNSH2sl","dateLastActive":"2022-10-07T15:12: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":[]},{"discussionID":96328,"type":"question","name":"Getting #invalid data type error for this formula","excerpt":"=COUNTIFS([Engagement Phase]:[Engagement Phase], \"COMPLETED\", YTD:YTD, YEAR(@cell) = 2021)","categoryID":322,"dateInserted":"2022-10-06T21:22:35+00:00","dateUpdated":null,"dateLastComment":"2022-10-07T15:54:12+00:00","insertUserID":153291,"insertUser":{"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-07T13:47:32+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":"2022-10-07T17:44:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":43,"score":null,"hot":3330254807,"url":"https:\/\/community.smartsheet.com\/discussion\/96328\/getting-invalid-data-type-error-for-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96328\/getting-invalid-data-type-error-for-this-formula","format":"Rich","lastPost":{"discussionID":96328,"commentID":346480,"name":"Re: Getting #invalid data type error for this formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/346480#Comment_346480","dateInserted":"2022-10-07T15:54:12+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":"2022-10-07T17:44:38+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-07T00:22:30+00:00","dateAnswered":"2022-10-06T21:31:59+00:00","acceptedAnswers":[{"commentID":346384,"body":"
<\/p>