☰

使用 Microsoft Excel 2013 计算工作时间

tag-icon • tag-icon worksheet-function equations
使用 Microsoft Excel 2013 计算工作时间

我正在尝试开发一个表格来计算我们技术人员的响应时间。

C 列表示接到电话的时间，F 列表示技术人员响应的时间。D 列和 E 列将从总体响应时间中减去（因为技术人员不会因订购零件而受到惩罚）。请注意，并非每个电话都需要订购零件，在这种情况下，D 列和 E 列将为空白。我希望表格排除周末和非工作时间（午休时间不计入响应时间）。如果电话是在工作时间以外或午餐时间打来的，则将其输入为下次恢复营业时发生的情况。

我一直在努力理解这一页但我无法这样做。

column A: customer
Column B: WO#
Column C: call received (mm/dd/yy hh:mm am/pm)
column D: parts ordered (mm/dd/yy hh:mm am/pm)
column E: parts received (mm/dd/yy hh:mm am/pm)
column F: dispatch time (mm/dd/yy hh:mm am/pm)
column G: response time (hh:mm) 
column H: response time (converted to fractional hours i.e. 2:15 would display in this column as 2.25)

business hours (j2: 8:00 am) (j3: 5:00 pm)
lunch break (j5: 12:00 pm)
lunch break end (j6: 1:00 pm)

所有日期和时间都以 Excel 日期/时间值的形式输入。我要查找的结果是响应时间。相同的值将出现在 G 列和 H 列中，但格式不同。

答案1

这个问题有大量的可能组合：

  • 工作周、工作日和午餐时间的来电时间
  • 是否需要零件
  • 当零件请求与工作周、工作日、午餐和呼叫到达相关时
  • 如果零件无法供应，是否要跨越一个周末甚至几个周末才能到达
  • 技术调度与电话到达和/或技术调度与零件到达是否跨越周末
  • 上述所有可能的组合和排列。

我看不出有什么办法可以用通用公式来计算。对于每个响应场景，你似乎都需要精确模拟每个活动相对于工作周、工作日和午餐时间的发生时间，并添加和减去相关时间。规则变得非常复杂，而且这还没有考虑假期，假期会增加一个额外的维度。

从技术上讲，使用一个“公式”来实现这一点是可行的，但它太长太复杂了，你永远无法排除故障或修改它，也无法在事后理解它。因此，这个答案将使用一堆构建块列（其中一些包含一些长公式），这些列会混合搭配并重复使用。它将为修改事物提供基础。

我创建了我能想到的最极端、最复杂的场景来测试它。这个解决方案可能过于复杂，因为有些情况在现实生活中不会发生。欢迎您简化它。

构建块列

日期和时间
Excel 将日期/时间存储为单个数字。整数部分是天数，小数部分是时间的十进制分数。公式的各个部分需要仅使用天数或时间，因此前八个构建块将它们分开。到K的列是到 的N列的天数，到 的列是时间。如何格式化这些列并没有太大区别。公式将产生数字（天数将是略小于 42000 的数字，时间将是十进制分数——中午或 12 小时将是 0.5）。假设您的数据从第 2 行开始：CFOR

K2:  =INT(C2)
L2:  =INT(D2)
M2:  =INT(E2)
N2:  =INT(F2)
O2:  =MOD(C2,1)
P2:  =MOD(D2,1)
Q2:  =MOD(E2,1)
R2:  =MOD(F2,1)

原始持续时间和零件订购时间
有几种方法可以处理零件订购时间。一种方法是计算持续时间并将其减去。在这个问题中，这又增加了复杂性，因为你不想重复计算午餐或周末的排除时间。我使用了不同的方法，在订购零件的情况下将问题分为两部分——订购零件之前发生的事情和收到零件之后发生的事情；中间的所有时间都被忽略。

这需要三个持续时间：

  • 致电接收零件订购
  • 零部件收据至技术调度
  • 如果不需要零件，请致电技术调度员

有些公式需要实际经过的时间，而有些公式则需要天数，与一天中的时间无关，因此有六个构建块列。这些持续时间减去周末。我假设周末可能出现在接到电话和下零件订单之间（电话在周五结束时到达），或者在周五结束时收到零件和周一技术人员调度之间。S到 的列U是实际经过的时间。V到 的列X是天数。

S2:  =D2-C2-IF(WEEKDAY(C2,2)+L2-K2>5,2,0)
T2:  =F2-E2-IF(N2-M2+WEEKDAY(E2,2)>5,2,0)
U2:  =F2-C2-INT((N2-K2+WEEKDAY(C2,2))/7)*2
V2:  =L2-K2-IF(WEEKDAY(C2,2)+L2-K2>5,2,0)
W2:  =N2-M2-IF(N2-M2+WEEKDAY(E2,2)>5,2,0)
X2:  =N2-K2-INT((N2-K2+WEEKDAY(C2,2))/7)*2

请注意，对于“无零件”的情况，周末的调整有所不同。这是因为我的极端测试案例包括接到电话和订购零件之间的一个周末、零件到达的长时间延迟以及零件到达和技术员派遣之间的另一个周末。对于“无零件”的情况，我只是使用了整个时间，其中包括两个周末，因此公式需要处理这个问题。如果您要解雇任何需要两周时间才能回复电话的技术人员，您可以将其更改为其他公式中使用的相同类型的单周末调整（如果您想要一致性）。

资格时间
下一步是处理工作日的时间范围。我将问题分为三个时间段。

  • 第一天（相关时间从呼叫到达开始，可以有多个结束点）
  • 最后一天（相关时间可以有多个起点，并以技术调度结束）
  • 中间天数（均为整天，需要根据周末进行调整）

所有活动都可能发生在一天或两天内，因此公式需要测试特定间隔是否存在且尚未考虑。根据是否需要订购零件，公式会有所不同，因此每个间隔都有两个公式（每个集合中的第一个是零件所需）。

First Day
Y2:  =IF(L2=K2,IF(M2=K2,IF(N2=K2,R2-Q2+P2,$J$3-Q2+P2),P2),$J$3)-O2
Z2:  =IF(N2=K2,R2,$J$3)-O2

Last Day
AA2:  =IF(M2=N2,IF(L2=N2,IF(K2=N2,0,P2-$J$2+R2-Q2),R2-Q2),R2-$J$2)
AB2:  =IF(K2=N2,0,R2-$J$2)

Days Between
AC2:  =IF(L2=K2,0,IF(M2=L2,IF(N2=L2,0,$J$3-Q2+P2-$J$2+(L2-K2-1-INT((L2-K2+WEEKDAY(C2,2))/7)*2)*
       ($J$3-$J$2)),P2-$J$2+(L2-K2-1-INT((L2-K2+WEEKDAY(C2,2))/7)*2)*($J$3-$J$2)))+
       IF(M2=L2,0,IF(N2=M2,0,$J$3-Q2+(N2-M2-1-INT((N2-M2+WEEKDAY(E2,2))/7)*2)*($J$3-$J$2)))
AD2:  =IF(N2-K2>1,N2-K2-1-INT((N2-K2+WEEKDAY(C2,2))/7)*2,0)*($J$3-$J$2)

请注意，为了便于阅读，我将较长的公式拆分了。如果要复制粘贴，则需要删除回车符和多余的空格。

午休
午休时间调整确定哪些天有适用的午休时间，计算这些天的午休时间，然后将计数乘以存储的午休时间规格。同样，需要零件的情况首先出现：

AE2:  =(IF(S2>$J$5-O2,1,0)+IF(S2>P2-$J$6,1,0)+IF(V2>=2,V2-1,0)+IF(T2>$J$5-Q2,1,0)+IF(T2>R2-$J$6,1,0)+IF(W2>=2,W2-1,0))*($J$6-$J$5)
AF2:  =(IF(U2>$J$5-O2,1,0)+IF(U2>R2-$J$6,1,0)+IF(X2>=2,X2-1,0))*($J$6-$J$5)

技术响应时间
然后，响应时间会选择适当的数字集进行组合（无论是否需要零件），并组合各个部分。单元格 G2：

G2:  =IF(ISBLANK(D2),Z2+AB2+AD2-AF2,Y2+AA2+AC2-AE2)

使用自定义格式。在格式下，选择接近的时间格式，然后在自定义窗口中对其进行自定义。您可以使用类似hh:mm或 的内容hh"h "mm"m"，这将产生类似 的结果03h 47m。

在 H 列中，您需要小时和小数小时。我在 Excel 中不经常进行时间格式化，但似乎内置格式只喜欢处理整小时。如果我错了，也许其他人可以用更好的格式化方法编辑这个答案。我只需将存储的数字转换为小时，然后将其格式化为具有所需小数位数的数字：

H2:  =G2*24

这将把一天的小数部分转换为小时数。

您可以根据需要将此行公式复制到页面下方。如果您不想查看构建块列，请隐藏这些列。

相关内容