基于特定时间班次的Excel工作小时数计算

ct3nt3jp 于 2023-02-10 发布在其他

关注(0)|答案(1)|浏览(185)

我有一个Excel格式的工作时间开始：15：30（A1）和完成：01：00（B1）。我想计算我上夜班的小时数（C1）。夜班是在22：00和06：00之间。因此，如果我工作，例如17：00到02：15，我应该得到数字04：15作为夜班小时数。如果我从22：00到09：15工作，我应该得到8个夜班小时数。我得到的时差为“"=MOD（B2-A2;1）"”但是这得到了整个轮班，而不仅仅是夜班。有什么建议吗？
看起来这段代码只适用于一对列，但是我不知道如何对结果求和。Excel是分隔成列的，每个日期都有2列用于开始和结束轮班
=MOD（D4-C4;1）24-（D4〈C4）（22-6）-中位数（D424;6;22）+中位数（C424;第6条、第22条）
理想的情况是得到该结果的公式=MOD（D4-C4;1）24-（D4〈C4）（22-6）-中位数（D424;6;22）+中位数（C424;6;22）+模块（F4-E4;1）24-（F4〈E4）（22-6）-中位数（F424;6;22）+中位数（E424;6;22）+模块（H4-G4;1）24-（H4〈G4）（22-6）-中位数（H424;6;22）+中位数（G424;6;22）等，但更紧凑的方式

excel

来源：https://stackoverflow.com/questions/75354352/excel-hours-worked-calculation-based-on-specific-time-shifts

1条答案

按热度按时间

brccelvz1#

假设所有的输入时间都是相对于1/0/1900的（只有时间部分信息），那么需要考虑日期何时是指第二天（加上1），结束日期的条件是如果start > end，那么end日期是指第二天（+1），我们在解决时考虑到了上述情况。
根据问题中列出的标签，假设没有excel版本约束，您可以在F2中使用以下公式：

=LET(start, A2:A5, end, B2:B5, ns, D2, ne, D3+1, eEff, IF(end<start, end+1, end),
 CALC, LAMBDA(isStart, MAP(start, eEff, LAMBDA(x,y, IF(y < ns, 0, 
  MEDIAN(IF(isStart, x, y), ns, ne))))), a, CALC(TRUE), b, CALC(FALSE),b-a)

我创建了一个用户LAMBDA函数CALC，只是为了避免在实际开始时重复类似的计算（a）和结束（b）。从公式中可以看出，夜间结束（ne）已经被调整为考虑第二天。类似地，我们对end时间进行调整，当结束时间（end）小于开始时间（start）时，考虑第二天计算有效结束时间（eEff）。
我们使用MEDIAN来标识两种情况下的实际间隔。