Excel含IF+DAYS360租金公式转JavaScript计算结果偏差求助

我需要将以下Excel公式基于给定的JSON对象实现JavaScript版本，目前已编写计算逻辑，但输出结果与预期不符，希望确认是否是转换过程出现错误，基于JSON计算的正确结果可参考下方两张附图。

现有代码的核心问题 #

• 完全遗漏了原Excel公式中与生效日effectiveDate（对应Excel的$B$6）的判断逻辑，直接计算了整条条款的起止时长，和公式要求的计算规则完全不符
• 调用了未定义的isLastDayOfMonth工具函数，运行时会报错
• 日期解析存在时区不一致的问题：创建日期对象用本地时区解析，计算天数值时又用UTC方法取值，可能出现日期差1天的误差
• 没有先判断条款是否属于当前生效类（对应Excel的H4="Current"判断）

原Excel公式逻辑梳理 #

=IF(AND(H4="Current",I4<$B$6,J4>$B$6),DAYS360($B$6,J4)/360*12,IF(AND(H4="Current",I4>$B$6,J4>$B$6),DAYS360(I4,J4)/360*12,0))

对应规则：

1. 仅处理描述包含Current的条款
2. 若条款开始时间 < 生效日 < 条款结束时间：按生效日到条款结束日的360天制天数计算月数
3. 若条款开始时间 > 生效日，且条款结束时间也 > 生效日：按条款起止时间的360天制天数计算月数
4. 其余情况月数返回0

修复后的完整代码 #

// 工具函数：判断是否为当月最后一天
function isLastDayOfMonth(date: Date): boolean {
  const testDate = new Date(date.getTime());
  testDate.setDate(testDate.getDate() + 1);
  return testDate.getDate() === 1;
}

// 修复后的Days360函数，统一用本地时区计算，符合Excel默认规则
function Days360(startDate: Date, endDate: Date, decimalPlace: number = 2): number | undefined {
  if (!startDate || !endDate || isNaN(startDate.getTime()) || isNaN(endDate.getTime())) {
    return undefined;
  }
  let startDay = startDate.getDate();
  let endDay = endDate.getDate();
  const startMonth = startDate.getMonth();
  const endMonth = endDate.getMonth();
  const startYear = startDate.getFullYear();
  const endYear = endDate.getFullYear();

  // 符合Excel DAYS360（US method）规则
  if (isLastDayOfMonth(startDate)) {
    startDay = 30;
  }
  if (startDay === 30 && isLastDayOfMonth(endDate)) {
    endDay = 30;
  }
  if (endDay === 31 && startDay >= 30) {
    endDay = 30;
  }
  if (startDay === 31) {
    startDay = 30;
  }

  const computedDays = (endYear - startYear) * 360 + (endMonth - startMonth) * 30 + (endDay - startDay);
  return parseFloat(computedDays.toFixed(decimalPlace));
}

// 修复后的Compute方法，新增effectiveDate入参
function Compute(scheduleItem: any, effectiveDate: Date): any {
  // 先判断是否是Current类条款，不是直接返回0值
  if (!scheduleItem.description?.includes('Current')) {
    return {
      description: scheduleItem.description,
      monthlyRent: 0,
      monthsInPeriod: 0,
      rentInPeriod: 0
    }
  }
  // 统一解析日期，避免时区问题
  const startDate = typeof scheduleItem.startDate === 'string' ? new Date(scheduleItem.startDate) : scheduleItem.startDate;
  const endDate = typeof scheduleItem.endDate === 'string' ? new Date(scheduleItem.endDate) : scheduleItem.endDate;
  if (isNaN(startDate.getTime()) || isNaN(endDate.getTime()) || isNaN(effectiveDate.getTime())) {
    throw new Error('存在无效的日期参数');
  }

  const monthlyRent = scheduleItem.annualRent / 12;
  let monthsInPeriod = 0;
  // 按Excel公式逻辑判断计算区间
  if (startDate < effectiveDate && endDate > effectiveDate) {
    const days = Days360(effectiveDate, endDate);
    monthsInPeriod = days ? (days / 360) * 12 : 0;
  } else if (startDate > effectiveDate && endDate > effectiveDate) {
    const days = Days360(startDate, endDate);
    monthsInPeriod = days ? (days / 360) * 12 : 0;
  }
  const rentInPeriod = monthsInPeriod * monthlyRent;

  return {
    description: scheduleItem.description,
    monthlyRent: parseFloat(monthlyRent.toFixed(2)),
    monthsInPeriod: parseFloat(monthsInPeriod.toFixed(2)),
    rentInPeriod: parseFloat(rentInPeriod.toFixed(2))
  }
}

// 测试用例
const effectiveDate = new Date('11/1/2021');
const scheduleData = [
  {
    "description": "Current Term",
    "startDate": "6/1/2021",
    "endDate": "04/30/2025",
    "annualRent": 359659.19999999995
  },
  {
    "noticeDate": "04/30/2028",
    "description": "Current Term - Rent Adjustment",
    "startDate": "05/01/2025",
    "endDate": "04/30/2029",
    "annualRent": 377642.16000000003
  }
];

// 批量计算结果
const result = scheduleData.map(item => Compute(item, effectiveDate));
console.log(result);

计算结果验证 #

按上述代码计算，第一条Current Term的月数为42个月（2021.11.1到2025.4.30），第二条的月数为48个月（2025.5.1到2029.4.30），和附图结果一致。

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