☰

计算开始时间 + 工作日和工作时间

tag-icon • tag-icon microsoft-excel worksheet-function date-time
计算开始时间 + 工作日和工作时间

我有以下问题：

在G4我有19/01/2016 10:00

在F4我有价值200 (units)

在E4我有价值38 (minutes)

我想计算G4 + (E4*F4)ie19/1/16 10:00 + 7,600 minutes但基于一个工作周：

Monday to Thursday 8am-5pm&Friday 8am-1pm

因此显示出09/02/2016 13:40

答案1

我对这个问题的理解是：

  • 有一套固定的生产计划，只在特定日期的特定时间段进行
  • 我们将忽略停工时间、午餐时间、休息时间等，并假设生产线在该工作期间一直在运行
  • 我们有给定的开始时间、要生产的单位数以及生产每个单位的时间
  • 我们需要确定生产何时完成

步骤1

为你的日程安排设置一张表

制作一个包含两周时间表的表格。我们需要它长达两周，因为这样我们就可以挑选第一个匹配的开始日期（例如星期二），然后包括接下来的 7 行，并知道我们有一个完整的工作周（例如星期二 - 星期一）。如果您没有两次时间表并且从星期五开始，那么您将得到一天，然后是一堆空白行。一定要包括星期六和星期日。这是我调用的表格的快照tblSchedule：

表计划

... 和 CSV 版本（时间以一天的一小部分显示）：

工作日,星期,开始时间,工作时间
1,星期日,0,0
2,星期一,0.333333333333333,9
3,星期二,0.333333333333333,9
4,星期三,0.333333333333333,9
5,星期四,0.333333333333333,9
6,星期五,0.333333333333333,5
7,星期六,0,0
1,星期日,0,0
2,星期一,0.333333333333333,9
3,星期二,0.333333333333333,9
4,星期三,0.333333333333333,9
5,星期四,0.333333333333333,9
6,星期五,0.333333333333333,5
7,星期六,0,0

第2步

为生产运行设置一个表格

您已经有了这个的开始。我们将添加一些字段，我假设您使用的是实际表格（插入功能区 > 表格）。如果您不这样做，这一切仍然有效，但理解公式将更加困难，因为它将是一堆单元格引用而不是字段名称。表格很棒。这是我所说的快照tblProduction：（请注意，我的系统使用非 ISO 格式来表示日期，m/d/yyyy因为美国。）

表格制作

... 以及 CSV 标题行：

最小/单位、数量单位、开始、生产时间（小时）、周、天、小时、结束

步骤 3

添加公式

生产时间（小时）

=[@[Min / Unit]]*[@[Qty Units]]/60

这一点很容易理解。唯一的技巧是，我们将分钟转换为小时，因为其余的数学运算将使用小时。

周数

=[@[Production Time (hrs)]]/(SUM(tblSchedule[Work Hours])/2)

这只是将小时数转换为工作周数。请注意，我们必须将一周内所有工作小时数的总和除以 2，因为我们的时间表是两周，而不是一周。

天

=MATCH(TRUE,INDEX((SUBTOTAL(9,OFFSET(tblSchedule[Work Hours],WEEKDAY([@Start])-1,0,ROW($A$1:$A$7)))-ROUND((((((TIME(HOUR([@Start]),MINUTE([@Start]),SECOND([@Start])))-INDEX(tblSchedule[Start Time],WEEKDAY([@Start])-1))*24))+(MOD([@Weeks],1)*(SUM(tblSchedule[Work Hours])/2))),2))>=0,0),0)-1

这个是巨大的怪物。如果有帮助的话，可以将其分成几部分，但我试图将其保持为一个漂亮的成品，我认为你的朋友不会想要里面的所有额外列。如果你要将其拆分，它可能看起来像这样：

扩展天数公式

... 以及 CSV 标题行：

天数、开始时间、工作开始时间、上周的小时数、自上周开始的小时数

开始时间

=TIME(HOUR([@Start]),MINUTE([@Start]),SECOND([@Start]))

工作开始时间

=INDEX(tblSchedule[Start Time],WEEKDAY([@Start])-1)

上周的小时数

=MOD([@Weeks],1)*(SUM(tblSchedule[Work Hours])/2)

自上周开始的小时数

=ROUND(((([@[Start Time]]-[@[Work Start Time]])*24)+[@[Hours in Last Week]]),2)

最后一个公式才是我们真正需要的。如果你展开这些列，公式将Days是这样的：

=MATCH(TRUE,INDEX((SUBTOTAL(9,OFFSET(tblSchedule[Work Hours],WEEKDAY([@Start])-1,0,ROW($A$1:$A$7)))-[@[Hours from Start of Last Week]])>=0,0),0)-1

基本思想是找到计划中的第一天，即从开始日开始的总工作时间大于完成生产所需的小时数。这就是它的作用MATCH(TRUE,INDEX((SUBTOTAL()-[Hours Left])>=0,0),0)-1。该SUBTOTAL函数执行了一些神奇的操作OFFSET并返回一个值数组。这些值是第一天、第二天、第三天等结束时的总工作时间。减去我们需要工作的剩余小时数，您会得到正数或负数（或零，因此是而>=0不是只是>0）。INDEX返回一个数组FALSE，TRUE因此MATCH找到第一个TRUE值。因为我们正在计算有多少更多的我们需要的天数不仅仅是第一天，最后减一。这是一个复杂的过程，需要得到一个介于 0 到 6 之间的数字。

小时

=(MOD([@Weeks],1)*(SUM(tblSchedule[Work Hours])/2))-IF([@Days]=0,0,SUM(OFFSET(tblSchedule[Work Hours],WEEKDAY([@Start])-1,0,[@Days])))

Hours in Last Week这个公式的开头和我们上面使用的公式一样。IF结尾的语句减去第一天和最后一天之间的工作时间（不包括）。如果我们从星期二开始，星期五结束，则减去星期三和星期四。这样我们就得到了最后一天（可能与第一天相同）必须工作的小时数。

结尾

=[@Start]+7*TRUNC([@Weeks])+[@Days]+[@Hours]/24

开始日期 + 7 *（周数为整数，删除小数部分）+ 天数 +（小时数转换为天数的十进制值）= 生产的结束日期和时间。

概括

是的，这些都是一些大公式。但是，我已经让它变得相当容易维护，并测试了几个不同的极端情况，所以我认为它都能正常工作。如果你的朋友想要一张整洁的表格，请使用 的大公式Days。如果他想要一个他可以更容易解释的，请使用带有几个辅助公式的版本。

添加

值得一提的是，您的朋友还可以添加现实世界的因素，例如预期废品率和停机时间（包括计划休息时间）。以下是考虑这些因素的示例前它们被输入到上面复杂的公式中。

拒收率

拒收率

以百分比形式手动输入。

生产时间（小时）

=[@[Min / Unit]]*([@[Qty Units]]/(1-[@[Reject Rate]]))/60

停机时间

标准时间

手动输入。与上面原始示例中使用的值相同。

停机时间

手动输入小时数，而不是百分比。您可以将其修改为百分比，但小时数更容易显示。

工作时间

=[@[Standard Hours]]-[@Downtime]

请注意，这可能会搞砸生产最后一天的实际结束时间。公式会假设您从早上 8 点开始工作并工作 X 小时，但实际上中间某处有休息时间。不过，它仍然可以作为近似值。

相关内容