Converting an Excel formula expression into JavaScript code

Question

Converting an Excel formula expression into JavaScript code

I am trying to implement the formula from Excel using the JSON object provided below. I have done the computations, but there seems to be an error as the result is not exact. If anyone has any ideas about it, I would appreciate your input. Thank you.

I just want to verify if I made a mistake in the conversion. The correct result should be based on the JSON data below:

https://i.sstatic.net/p1Nwm.png

https://i.sstatic.net/0XLJ2.png

#FORMULA

=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))

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

effectiveDate = new Date('11/1/2021')
    
    
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
        }
    ]


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

javascript typescript

Answer 1

Answer №1

One aspect of code that I overlooked checking was the isLastDayOfMonth function. After testing your code with a modified version of isLastDayOfMonth, it successfully produced the desired outcome:

function isLastDayOfMonth(inDate: Date): boolean {
  //april,june,sept,nov
  const thirties = [3, 5, 8, 10];
  //jan,mar,may,jul,aug,oct,dec
  const thOnes = [0, 2, 4, 6, 7, 9, 11];
  if (thirties.includes(inDate.getMonth())) { return inDate.getDate() === 30; }
  if (thOnes.includes(inDate.getMonth())) { return inDate.getDate() === 31; }
  if (inDate.getFullYear() % 4 === 0) { return inDate.getDate() === 29; }
  return inDate.getDate() === 28;
}

Update:

I failed to account for the Effective Date in your initial code, which led to discrepancies. While the spreadsheet indicated 41.97 for the first line, I was obtaining 46.97.

By incorporating the following code (in TS Playground), I achieved accurate results:

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

function 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') {
    return scheduleData;
  }
  let monthlyRent = scheduleData.annualRent / 12;
  let range360: any = (startDate < effectiveDate && endDate > effectiveDate) ?
    Days360(effectiveDate, endDate) :
    (startDate > effectiveDate && endDate > effectiveDate) ?
      Days360(startDate, endDate) :
      0;
  let monthsInPeriod = (range360 / 360) * 12;
  let rentInPeriod = monthsInPeriod * monthlyRent;
  return {
    description: scheduleData.description,
    monthlyRent: monthlyRent,
    monthsInPeriod: monthsInPeriod,
    rentInPeriod: rentInPeriod
  };
}

function Days360(startDate: Date, endDate: Date, decimalPlace: number = 2): number | undefined {
  if (!startDate || !endDate) {
    return undefined;
  }
  let startMonth = startDate.getMonth();
  let startDayOfMonth = startDate.getDate();
  let startYear = startDate.getFullYear();
  let endMonth = endDate.getMonth();
  let endDayOfMonth = endDate.getDate();
  let endYear = endDate.getFullYear();

  let monthsDifference = endMonth - startMonth;
  let dayOfMonthDifference = endDayOfMonth - startDayOfMonth;
  let yearDifference = endYear - startYear;

  let monthsDifferenceInDate360 = 30 * monthsDifference;
  let yearDifferenceInDate360 = 360 * yearDifference;

  let differenceSum = yearDifferenceInDate360 + monthsDifferenceInDate360 + dayOfMonthDifference;
  return parseFloat(parseFloat(differenceSum.toString()).toFixed(decimalPlace));
}
var 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
}
];

scheduleData.forEach(function (sd): void {
  console.log(Compute(sd));
});

Answer 2

One aspect of code that I overlooked checking was the isLastDayOfMonth function. After testing your code with a modified version of isLastDayOfMonth, it successfully produced the desired outcome:

function isLastDayOfMonth(inDate: Date): boolean {
  //april,june,sept,nov
  const thirties = [3, 5, 8, 10];
  //jan,mar,may,jul,aug,oct,dec
  const thOnes = [0, 2, 4, 6, 7, 9, 11];
  if (thirties.includes(inDate.getMonth())) { return inDate.getDate() === 30; }
  if (thOnes.includes(inDate.getMonth())) { return inDate.getDate() === 31; }
  if (inDate.getFullYear() % 4 === 0) { return inDate.getDate() === 29; }
  return inDate.getDate() === 28;
}

Update:

I failed to account for the Effective Date in your initial code, which led to discrepancies. While the spreadsheet indicated 41.97 for the first line, I was obtaining 46.97.

By incorporating the following code (in TS Playground), I achieved accurate results:

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

function 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') {
    return scheduleData;
  }
  let monthlyRent = scheduleData.annualRent / 12;
  let range360: any = (startDate < effectiveDate && endDate > effectiveDate) ?
    Days360(effectiveDate, endDate) :
    (startDate > effectiveDate && endDate > effectiveDate) ?
      Days360(startDate, endDate) :
      0;
  let monthsInPeriod = (range360 / 360) * 12;
  let rentInPeriod = monthsInPeriod * monthlyRent;
  return {
    description: scheduleData.description,
    monthlyRent: monthlyRent,
    monthsInPeriod: monthsInPeriod,
    rentInPeriod: rentInPeriod
  };
}

function Days360(startDate: Date, endDate: Date, decimalPlace: number = 2): number | undefined {
  if (!startDate || !endDate) {
    return undefined;
  }
  let startMonth = startDate.getMonth();
  let startDayOfMonth = startDate.getDate();
  let startYear = startDate.getFullYear();
  let endMonth = endDate.getMonth();
  let endDayOfMonth = endDate.getDate();
  let endYear = endDate.getFullYear();

  let monthsDifference = endMonth - startMonth;
  let dayOfMonthDifference = endDayOfMonth - startDayOfMonth;
  let yearDifference = endYear - startYear;

  let monthsDifferenceInDate360 = 30 * monthsDifference;
  let yearDifferenceInDate360 = 360 * yearDifference;

  let differenceSum = yearDifferenceInDate360 + monthsDifferenceInDate360 + dayOfMonthDifference;
  return parseFloat(parseFloat(differenceSum.toString()).toFixed(decimalPlace));
}
var 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
}
];

scheduleData.forEach(function (sd): void {
  console.log(Compute(sd));
});

Converting an Excel formula expression into JavaScript code

Answer №1

Similar questions

How to specify in TypeScript that if one key is present, another key must also be present, without redundantly reproducing the entire structure

Ways to verify partially executed php code with the aid of javascript

Tips for creating a helper function that returns a promise for a React hook function

Discovering the window.scrollTop, ScrollY, or any other distance value while utilizing CSS scroll snap can be achieved by following these

What could be causing my JavaScript to not execute when going back on Safari?

Display information from a specific identifier in a modal popup window after clicking a button using PHP and MySQL

"Overabundance of recursion" error encountered upon submission via jQuery

Having trouble retrieving the contents of a <div> tag within a JavaScript function

When using jQuery's .ajax method, the function is not being called

Type validation in TypeScript through cross-referencing variables

Is it possible to modify the CSS produced by Bootstrap in an Angular application?

JavaScript Astro file not triggering window.onload event

Every variable declaration that follows must match the data type of the initial declaration

Make sure to always send back JSON data when using the default error handler in ExpressJS

What is the best way to retrieve an array of objects from Firebase?

What programming language is the best choice for Angular 2 development?

When you attempt to "add website to homescreen" on an iPhone, Ajax malfunctions

`My jquery mobile application fails to trigger the pageinit or ready events`

"Encountering a problem with the client-session middleware: the value of req.session_state is becoming undefined

An Alternative Approach to Implementing the Ternary Operator in JavaScript