excel - Excel。日期和时间 = 轮班 X

Question

我有一个格式为日期和时间的单元格：(dd-mm-yyyy tt:mm)
基于此，我想提取在我的 excel 文件中生成数据的班次。

换班时间如下

• 白班：06:01-14:00（周一至周四）06:00-13:20（周五）
  
• 夜班：14:01-22:50（周一至周四）
  
• 夜班：22:36-06:00（周日晚上-周一早上）22:51-06:00（周一-周五）
  
• 周末班：13:21-01:20（周五） 10:40-22:35（周日）
  
• 这些班次之外的任何事件都应作为班次返回：加班
  
  我已经@this 几个小时没有结果。有人愿意挑战吗？:)

标签： exceldatetimeif-statementvlookupweekday

Answer 1

我认为这样做的方法是设置一个表格，其中包含各种参数的列，以及工作日的计算列。

我为表命名Shifts并使用结构化引用以便于维护和将来理解，但如果必须，您可以使用常规引用。

班次表

E2中的公式：

=MATCH([@Day],{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"},0)

在给定时间戳的情况下，将返回 Shift 的公式：

=IFERROR(
       LOOKUP(2,1/(
              (MOD(timeStamp,1)>=Shifts[startTime])*
              (MOD(timeStamp,1)<=Shifts[endTime])*
              (WEEKDAY(timeStamp)=Shifts[wdNum])),Shifts[Shift]),
      "Overtime")

随机结果

在带有结构化引用的表中使用公式

=IFERROR(
       LOOKUP(2,1/(
              (MOD([@timeStamp],1)>=Shifts[startTime])*
              (MOD([@timeStamp],1)<=Shifts[endTime])*
              (WEEKDAY([@timeStamp])=Shifts[wdNum])),Shifts[Shift]),
      "Overtime")