按月安排的小时数公式

SarahALLBRiGHT · 04/11/22

我们试图根据项目的开始日期和结束日期确定我们在一个月内为特定项目安排了多少小时。我不太明白如何在没有EOM功能的情况下设置MAX/MIN。有人能帮帮我吗?

吉纳维芙P。 · 04/13/22

嗨@SarahALLBRiGHT

我的方法是首先找出每个月的工作天数，然后乘以“每天的小时数”。

我们可以使用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”))))

让我知道这是否适合你!

欢呼,

吉纳维芙

保罗新来的 · 04/13/22

除了…之外@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

吉纳维芙P。 · 04/13/22

哈哈,@Paul新来的用一个更简洁的公式来挽回局面。谢谢你！

保罗新来的 · 04/13/22

@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) +“小时”)))

吉纳维芙P。 · 04/13/22

https://media.giphy.com/media/udmx3pgdiD7tm/giphy.gif

SarahALLBRiGHT · 04/13/22

哇，你们都是巫师!它工作!

然而，我只是在寻找返回小时数的公式，这样我们就可以计算出当月所有工作的总小时数。你能提供修改后的配方吗?

我的歉意。我的例子不太清楚。我写出了天数和小时数，以显示公式如何将一个月的工作日数除以，从而得到该月安排的小时数。

吉纳维芙P。 · 04/13/22

嗨@SarahALLBRiGHT

你每天有固定的时间吗?例如:每天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”)))

这就是你要找的吗?

SarahALLBRiGHT · 04/13/22

@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))))

或者我们应该用另一种方法。也许可以用该月生产的小时数除以项目的总人工小时数，得到该月生产的小时数的百分比然后乘以总人工小时数?

保罗新来的 · 04/14/22

您是否有针对二月、三月、四月等月份的附加专栏?

你是如何使用这些总数的?它们是否具有严格的信息，或者你是否将它们拉入图表的参数表中?

你可能需要考虑的最长时间是多少?

SarahALLBRiGHT · 04/14/22

嗨@Paul新来的！是的，我每个月有一个专栏。从那里，我们将把每月的总数拉到生产仪表板上的一个度量表中，这样我们就可以看到我们这个月的预订情况。ob欧宝娱乐app手机下载我想，最长的持续时间是4-5个月。谢谢你的参与。感觉我们很接近了!

保罗新来的 · 04/14/22

嗯…好的。我觉得我们应该快找到答案了。

我们知道我们需要工作日。函数的开始日期将是每月的第一天或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函数的月份部分。

我还没有机会测试这个。如果我在你之前得到一个机会，我会回复它是为我工作还是一个不同的公式。

保罗新来的 · 04/14/22

看起来我遗漏了几个方括号，我添加了一个=(测试公式是针对当前月份的)。

=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

还要记住……如果开始日期或结束日期在不同的年份，这将不起作用。如果你需要适应这一点，那么我的建议是进行一些重组。如果是这样的话，请告诉我，如果你有兴趣的话，我很乐意帮助你。

凯拉 · 05/12/23

@Paul新来的-我试图做同样的事情作为这个线程，但在一个交叉表，这样我就可以跨越多年，我不需要在源表每月列。

“时间表”表中有数据，我想在这个指标表中计算“总工作时间”。

任何想法或帮助将非常感激!!

保罗新来的 · 05/13/23

@Kayla您需要在源工作表上创建额外的列，并在那里使用公式输出每个月每行工作了多少小时。然后，您的指标表将使用一个基本的SUM函数(或SUMIFS，取决于您的结构)从每个helper列中抓取数据。

上面可以找到分散每个月的天数的解决方案。

按月安排的小时数公式

答案

帮助文章参考资料欧宝体育app官方888

类别