☰

Excel 公式：找出特定时间范围内花费的时间

tag-icon • tag-icon microsoft-excel worksheet-function time date-time
Excel 公式：找出特定时间范围内花费的时间

尝试在这里制定稍微复杂一点的加班工作表：

员工获得报酬：

  • 工作日（周一至周五）08:30 至 17:30 期间按 1x 小时费率收费
  • 工作日（周一至周五）08:30 之前或 17:30 之后按 1.5 倍小时费率收费
  • 周六每小时工资为 1.5 倍
  • 周日和公共假日按 2 倍小时费率收费

工作表布局：

|Date   |Day    |Slip No    |Name   |Destination    |Start Time |End Time   |Total Hours Worked |Basic Hours    |OT @ 1.5   |OT @ 2.0

所以我在寻找列的公式

  • [H] = 工作小时数
  • [I] = 基本小时数
  • [J] = 加时赛@1.5
  • [K] = 加时赛 @ 2.0

我只是在寻找以 hh:mm 表示的工作内容；因此不需要“小时费率”字段。

[H] =MOD(G6-F6,1)
[I] =IF(F6<G6,MIN(G6,Data!F2)-MAX(F6,Data!E2),MAX(0,Data!F2-F6)+MAX(0,G6-Data!E2))
[J] =H6-I6
[K] =IF(OR(WEEKDAY($A6)=1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=FALSE),$H6,0)

[H] = 工作；

[K] = 工作；

[I]/[J] 是问题所在，我觉得如果我能让 I 工作，我也能为 J 找到解决方案。J 就是“总小时数”减去“基本小时数”。

tblPublicHolidays 是我们的公共假期列表。

数据！E2 = 开始时间 - 即：08:30

Data!F2 = 结束时间 - 即：17:30

为了演示的目的，第一行 (6) 的开始时间为 18:30，结束时间为 18:45。我从中获得了 [I] 公式https://exceljet.net/formula/total-hours-that-fall-between-two-times但由于以下原因，它似乎不起作用：

  • 如果我的格式为“时间”，则 [I] 会显示为一堆井号 (#)
  • 如果我将 [I] 更改为数字格式，则结果为 -0.04
  • [J] 似乎总是比预期多出 1 个小时（在这个例子中是 1:15 而不是 0:15）

答案1

谢谢您的帮助，但是这是我最终得到的结果（假设起始行为 6）：

A6 (DATE)
B6 (DAY) =IF(A6<>"",TEXT(A6,"dddd"),"")
F6 (START TIME)
I6 (END TIME)
L6 (HOURS WORKED) =IF(AND(F6>0,I6>0),MOD(I6-F6,1)*24,"")
M6 (BASIC HOURS) =IF(AND(F6>0,I6>0),IF(AND(WEEKDAY($A6)>1,WEEKDAY($A6)<7),IF(F6 < I6,MAX(0,MIN(I6,upper)-MAX(F6,lower))*24,MAX(0,upper - F6)+MAX(0,I6 - lower)*24),0),"")
N6 (Overtime @ 1.5) =IF(AND($F6>0,$I6>0),IF(AND(WEEKDAY($A6)>1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=TRUE),(L6-M6),0),"")
O6 (Overtime @ 2.0) =IF(AND($F6>0,$I6>0),IF(OR(WEEKDAY($A6)=1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=FALSE),$L6,0),"")

相关内容