操作的每个小时中的员工
我试图获得每小时员工数量的人数。最早的轮班从03:00开始，最新的班次结束在06:00（第二天）。我使用的公式是从小时03：00-23：00返回正确的员工。但是从23：00-07：00（第二天）车长关闭。我使用的配方23：00-00：00是：
=COUNTIFS('May 2-May 8'!$D:$D, ">"&AC$30, 'May 2-May 8'!$C:$C,"<"&AD$30, 'May 2-May 8'!$H:$H, "SKD", 'May 2-May 8'!$I:$I, "CREW CHIEF") + COUNTIFS('May 2-May 8'!$D:$D, "<="&$I$30, 'May 2-May 8'!$C:$C,"<"&AC$30, 'May 2-May 8'!$H:$H, "SKD", 'May 2-May 8'!$I:$I, "CREW CHIEF").
我正在使用的公式00：00-07：00是：
=COUNTIFS('May 2-May 8'!$D:$D, "<="&$L$30, 'May 2-May 8'!$C:$C,">="&V$30, 'May 2-May 8'!$H:$H, "SKD", 'May 2-May 8'!$I:$I, "CREW CHIEF").
我尝试了很多方法来弄清楚这一点，但由于某种原因，我无法明白。
谢谢
I am trying to get an headcount of the amount of employees I have per hour. The earliest shift starts at 03:00 and the latest shift end at 06:00 (the next day). The formula that I am using is returning a correct headcount from hours 03:00-23:00. But from 23:00-07:00 (next day) the headcount is off. The formula I am using for 23:00-00:00 is:
=COUNTIFS('May 2-May 8'!$D:$D, ">"&AC$30, 'May 2-May 8'!$C:$C,"<"&AD$30, 'May 2-May 8'!$H:$H, "SKD", 'May 2-May 8'!$I:$I, "CREW CHIEF") + COUNTIFS('May 2-May 8'!$D:$D, "<="&$I$30, 'May 2-May 8'!$C:$C,"<"&AC$30, 'May 2-May 8'!$H:$H, "SKD", 'May 2-May 8'!$I:$I, "CREW CHIEF").
The formula I am using for 00:00-07:00 is:
=COUNTIFS('May 2-May 8'!$D:$D, "<="&$L$30, 'May 2-May 8'!$C:$C,">="&V$30, 'May 2-May 8'!$H:$H, "SKD", 'May 2-May 8'!$I:$I, "CREW CHIEF").
I tried so many ways to figure this out myself, but for some reason I cannot get it.
Thank you
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要简化数据集，以避免执行复杂的公式。
您的问题非常容易解决。
您需要添加两个额外的列，以进行换档启动和移位端，也可以编辑现有列。而不是时间格式，您应该列出日期和时间格式。那很容易。
= countifs（table_start_datetime]，“＆lt; =”＆amp; $ k $ 1+j5，table1 [shift_end_dateTime]，“＆gt; =”＆amp; $ k $ 1+j5）
You need to simplify your dataset in order to avoid performing complex formula.
the problem you have its very easy to solve.
you need to add two additional columns for the shift start and shift end or you can edit your existing columns. instead of time format you should put date and time format. then its very easy.
=COUNTIFS(Table1[SHIFT_START_DATETIME],"<="&$K$1+J5,Table1[SHIFT_END_DATETIME],">="&$K$1+J5)