Google Apps Script跨天夜班工时含休息扣除计算错误排查

• 跨天班次未拆分处理：原脚本仅按单日逻辑计算，导致次日6:00-7:00的正常班时段被错误归入夜班，未单独统计。
• 跨天休息时段未检测：夜班休息时段为04:00-04:30（次日），跨天班次（22:00-次日7:00）覆盖该时段，但原脚本未识别，因此未触发休息扣除。
• 休息扣除逻辑未适配跨天场景：总工时9小时（超6小时）且包含休息时段，应扣除30分钟，但原脚本未结合跨天情况判断扣除条件。

1. 拆分跨天班次为单日时段 #

实现函数判断班次是否跨天，若跨天则拆分为「当日开始-当日24:00」和「次日00:00-次日结束」两个独立时段，分别计算每个时段的班次工时。

function splitShift(start, end) {
  const startDate = new Date(start);
  const endDate = new Date(end);
  const shifts = [];
  if (endDate <= startDate) {
    // 拆分跨天班次
    const endOfDay = new Date(startDate);
    endOfDay.setHours(23, 59, 59, 999);
    shifts.push({start: startDate, end: endOfDay});
    
    const startOfNextDay = new Date(endDate);
    startOfNextDay.setHours(0, 0, 0, 0);
    shifts.push({start: startOfNextDay, end: endDate});
  } else {
    shifts.push({start: startDate, end: endDate});
  }
  return shifts;
}

2. 按时段计算对应班次工时 #

针对每个拆分后的时段，计算其与正常班、晚班、夜班的交集时长，确保次日的正常班时段被正确统计。

// 计算两个时间区间的交集时长（毫秒）
function getOverlap(start1, end1, start2, end2) {
  const overlapStart = new Date(Math.max(start1.getTime(), start2.getTime()));
  const overlapEnd = new Date(Math.min(end1.getTime(), end2.getTime()));
  return overlapEnd > overlapStart ? overlapEnd.getTime() - overlapStart.getTime() : 0;
}

function calculateShiftHours(shift) {
  const start = shift.start;
  const end = shift.end;
  const hours = {normal: 0, evening: 0, night: 0};
  
  // 正常班：06:00-18:00
  const normalStart = new Date(start);
  normalStart.setHours(6, 0, 0, 0);
  const normalEnd = new Date(start);
  normalEnd.setHours(18, 0, 0, 0);
  hours.normal += getOverlap(start, end, normalStart, normalEnd) / 3600000;
  
  // 晚班：18:00-21:00
  const eveningStart = new Date(start);
  eveningStart.setHours(18, 0, 0, 0);
  const eveningEnd = new Date(start);
  eveningEnd.setHours(21, 0, 0, 0);
  hours.evening += getOverlap(start, end, eveningStart, eveningEnd) / 3600000;
  
  // 夜班：当日21:00-24:00 + 次日00:00-06:00
  const night1Start = new Date(start);
  night1Start.setHours(21, 0, 0, 0);
  const night1End = new Date(start);
  night1End.setHours(23, 59, 59, 999);
  hours.night += getOverlap(start, end, night1Start, night1End) / 3600000;
  
  // 处理次日的夜班时段（仅当当前时段是跨天的第二部分时）
  if (start.getDate() !== end.getDate()) {
    const night2Start = new Date(end);
    night2Start.setHours(0, 0, 0, 0);
    const night2End = new Date(end);
    night2End.setHours(6, 0, 0, 0);
    hours.night += getOverlap(start, end, night2Start, night2End) / 3600000;
  }
  
  return hours;
}

3. 适配跨天的休息扣除逻辑 #

先检测班次是否覆盖对应休息时段，再根据总工时判断扣除时长：

• 工时不足6小时：无扣除
• 满6小时且含休息时段：扣20分钟
• 超6小时且含休息时段：扣30分钟

function hasRestPeriod(shifts) {
  let hasNormalRest = false;
  let hasEveningRest = false;
  let hasNightRest = false;
  
  shifts.forEach(shift => {
    const start = shift.start;
    const end = shift.end;
    
    // 正常班休息：12:00-12:30
    const normalRestStart = new Date(start);
    normalRestStart.setHours(12, 0, 0, 0);
    const normalRestEnd = new Date(start);
    normalRestEnd.setHours(12, 30, 0, 0);
    if (getOverlap(start, end, normalRestStart, normalRestEnd) > 0) hasNormalRest = true;
    
    // 晚班休息：20:00-20:30
    const eveningRestStart = new Date(start);
    eveningRestStart.setHours(20, 0, 0, 0);
    const eveningRestEnd = new Date(start);
    eveningRestEnd.setHours(20, 30, 0, 0);
    if (getOverlap(start, end, eveningRestStart, eveningRestEnd) > 0) hasEveningRest = true;
    
    // 夜班休息：04:00-04:30（跨天场景取次日日期）
    const restDate = start.getDate() === end.getDate() ? start : end;
    const nightRestStart = new Date(restDate);
    nightRestStart.setHours(4, 0, 0, 0);
    const nightRestEnd = new Date(restDate);
    nightRestEnd.setHours(4, 30, 0, 0);
    if (getOverlap(start, end, nightRestStart, nightRestEnd) > 0) hasNightRest = true;
  });
  
  return {hasNormalRest, hasEveningRest, hasNightRest};
}

function calculateRestDeduction(totalHours, hasRest) {
  if (!hasRest) return 0;
  if (totalHours >= 6 && totalHours <= 6) return 20 / 60; // 20分钟转小时
  if (totalHours > 6) return 30 / 60; // 30分钟转小时
  return 0;
}

4. 整合主函数 #

将以上逻辑整合，计算最终的工时统计结果：

function calculateTotalHours(startTime, endTime) {
  const shifts = splitShift(startTime, endTime);
  let total = {normal: 0, evening: 0, night: 0};
  
  // 累加各时段的班次工时
  shifts.forEach(shift => {
    const shiftHours = calculateShiftHours(shift);
    total.normal += shiftHours.normal;
    total.evening += shiftHours.evening;
    total.night += shiftHours.night;
  });
  
  const totalHours = total.normal + total.evening + total.night;
  const restStatus = hasRestPeriod(shifts);
  
  // 扣除休息时间
  total.normal -= calculateRestDeduction(totalHours, restStatus.hasNormalRest);
  total.evening -= calculateRestDeduction(totalHours, restStatus.hasEveningRest);
  total.night -= calculateRestDeduction(totalHours, restStatus.hasNightRest);
  
  // 格式化输出（整数显示整数，否则保留1位小数）
  return {
    normal: total.normal % 1 === 0 ? total.normal.toString() : total.normal.toFixed(1),
    evening: total.evening % 1 === 0 ? total.evening.toString() : total.evening.toFixed(1),
    night: total.night % 1 === 0 ? total.night.toString() : total.night.toFixed(1)
  };
}

// 测试案例：22:00-次日07:00
const testStart = new Date('2024-05-20T22:00:00');
const testEnd = new Date('2024-05-21T07:00:00');
console.log(calculateTotalHours(testStart, testEnd)); // 输出 {normal:'1',evening:'0',night: '7.5'}

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