按小时与工作日统计在岗坐席数：跨午夜统计错误修复

我需要依据坐席的常规排班表，统计指定小时和工作日的在岗坐席数量，但当前方法无法准确统计跨午夜工作的坐席。例如，若坐席工作至次日凌晨2点，系统会错误地将其统计到开始工作的日期，而非实际在岗的次日。

排班表记录了销售坐席的工作起止时间及每周工作日安排，我的目标是精准统计一周内每日各时段的在岗坐席数，已尝试以下公式但均未达到预期效果：

=ARRAYFORMULA( SUM( IF( ($M2 >= $A$2:$A$100) * ($M2 < $B$2:$B$100), IF( OFFSET(D$2, 0, MATCH(N$1, D$1:J$1, 0)-1, COUNTA(D$2:D$100), 1) = "ON", 1, 0 ), 0 ) ) + SUM( IF( ($M2 < $B$2:$B$100) * ($A$2:$A$100 > $B$2:$B$100), IF(N$1="Monday", IF(J$2:J$100 = "ON", 1, 0), IF( OFFSET(D$2, 0, IF(MATCH(N$1, D$1:J$1, 0)-1=0, 6, MATCH(N$1, D$1:J$1, 0)-2), COUNTA(D$2:D$100), 1) = "ON", 1, 0 ) ), 0 ) ) )

OR

=ARRAYFORMULA( SUM( IF( ($M2 >= $A$2:$A$100) * ($M2 < $B$2:$B$100) + (($M2 < $B$2:$B$100) * ($A$2:$A$100 > $B$2:$B$100)), IF( OFFSET(D$2, 0, MATCH(N$1, D$1:J$1, 0)-1, COUNTA(D$2:D$100), 1) = "ON", 1, 0 ), 0 ) ) )

OR

=ARRAYFORMULA(SUM( ( (($M2 >= $A$2:$A$100) * ($M2 < $B$2:$B$100) + ($M2 >= $A$2:$A$100) * ($A$2:$A$100 > $B$2:$B$100) + ($M2 < $B$2:$B$100) * ($A$2:$A$100 > $B$2:$B$100)) * (OFFSET(D$1,0,MATCH(N$1, $D$1:$J$1, 0)-1, COUNTA(D$2:D$100), 1) = "ON") ), 1, 0))-1 
=ARRAYFORMULA(SUM( ( (($M2 >= $A$2:$A$100) * ($M2 < $B$2:$B$100) + ($M2 >= $A$2:$A$100) * ($A$2:$A$100 > $B$2:$B$100) + ($M2 < $B$2:$B$100) * ($A$2:$A$100 > $B$2:$B$100)) * (OFFSET(D$2,0,MATCH(N$1, D$1:J$1, 0)-1, COUNTA(D$2:D$100), 1) = "ON") ), 1, 0))-1

内容的提问来源于stack exchange，提问作者Mike