converting excel formula to javascript code-CodePudding

I was trying to get the Months in period , rent in period and monthly rent and the remaining obligation which is the total based on the formula/ using the excel formula below , I have my JS code with it but I can't get the exact result I would like to ask for help if my convertion to JS is correct or what seems to be the issue.

Would really appreciate if someone can point the issue. Thank you.

#sample data input (the scheduleData object and effectiveDate input)

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
    }
]

#formula 1 = ANSWER IS : 41.97 =IF(AND(H4="Current",I4<$B$6,J4>$B$6),DAYS360($B$6,J4)/36012,IF(AND(H4="Current",I4>$B$6,J4>$B$6),DAYS360(I4,J4)/36012,0))

#formula 2 = ANSWER IS 47.97 =IF(AND(H5="Current",I5<$B$6,J5>$B$6),DAYS360($B$6,J5)/36012,IF(AND(H5="Current",I5>$B$6,J5>$B$6),DAYS360(I5,J5)/36012,0))

#getting the total #js code

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));
  }

CodePudding user response：

Your Days360 implementation is not working properly. The following code is tested to behave the same way as 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));
}