javascript - 将excel公式转换为javascript代码

Question

我试图获得期间的月份、期间的租金和月租金以及剩余的义务，这是基于公式/使用下面的 excel 公式的总数，我有我的 JS 代码，但我无法得到确切的结果如果我的 JS 转换是正确的或者似乎是什么问题，我想寻求帮助。

如果有人能指出这个问题，我将不胜感激。谢谢你。

#sample 数据输入（scheduleData 对象和 EffectiveDate 输入）

effectiveDate = new Date('11/1/2021')


[
    {
        "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
    }
]

#公式 1 = 答案是：41.97 =IF(AND(H4="Current",I4<$B$6,J4>$B$6),DAYS360($B$6,J4)/360 12,IF(AND(H4= "当前",I4>$B$6,J4>$B$6),DAYS360(I4,J4)/360 12,0))

#公式 2 = 答案为 47.97 =IF(AND(H5="当前",I5<$B$6,J5>$B$6),DAYS360($B$6,J5)/360 12,IF(AND(H5="当前",I5>$B$6,J5>$B$6),DAYS360(I5,J5)/360 12,0))

#获取总 #js代码

Compute(scheduleData: any):any{
    let startDate = typeof scheduleData.startDate === 'string' ? new Date(scheduleData.startDate):scheduleData.startDate;
    const endDate = typeof scheduleData.endDate === 'string' ? new Date(scheduleData.endDate):scheduleData.endDate;
    if(!startDate || startDate.toString() === 'Invalid Date'){
    }
    let monthlyRent = scheduleData.annualRent / 12;
    let monthsInPeriod = (this.Days360(startDate, endDate) / 360) * 12 
    let rentInPeriod = monthsInPeriod * monthlyRent;
    return { 
      description: scheduleData.description,
      monthlyRent: monthlyRent,
      monthsInPeriod: monthsInPeriod,
      rentInPeriod: rentInPeriod
    }
  }

  Days360(startDate: Date, endDate: Date, decimalPlace: number = 2){
    if(!startDate || !endDate){
      return undefined;
    }
    let startDay = startDate.getUTCDate();
    let endDay = endDate.getUTCDate();

    if(isLastDayOfMonth(startDate)){
      startDay = 30;
    }

    if(isLastDayOfMonth(startDate) && isLastDayOfMonth(endDate)){
      endDay = 30;
    }
    const computedDays = (((endDate.getUTCFullYear() - startDate.getUTCFullYear()) * 360) + ((endDate.getUTCMonth() - startDate.getUTCMonth()) * 30) + (endDay - startDay));
    return parseFloat(parseFloat(computedDays.toString()).toFixed(decimalPlace));
  }

标签： javascriptangulartypescript

Answer 1

您的Days360实施无法正常工作。以下代码经过测试，其行为方式与 Excell 相同DAYS360：

function Days360(start: Date, end: Date, decimalPlace: number = 2){
    if(!start || !end) return undefined;
    const days = (end.getUTCFullYear() - start.getUTCFullYear()) * 360
        + (end.getUTCMonth() - start.getUTCMonth()) * 30
        + (end.getUTCDate() - start.getUTCDate());
    return parseFloat(parseFloat(days.toString()).toFixed(decimalPlace));
}