The math equations I used in a Google Sheets spreadsheet aren't working in my JavaScript code (though they should be) and I can't figure out why

Question

Note: I did my best to explain this problem as clearly as I could, so I'm sorry if this is a little lengthy. I just wanted to be clear.

I created a bunch of tools for the mobile game Lord's Mobile a while back using Google Sheets, and am currently in the process of transferring these tools onto a new website. Unfortunately, I'm having issues with the code for my "Helps Calculator" tool.

In this game, a "help" is something given by a clanmate to assist a player in reducing the time on one of their upgrades. So if I start a 3-hour upgrade and a clanmate gives me a "help", it reduces my time by a certain amount. What this tool does is tell the user how much time will remain after a certain number of helps are given. I would input my time duration (3 hours) in the top row, and then refer to the table to see how much time remains after each "help" given.

All relevant documents related to this question are linked here, including the original spreadsheet tool, the live demo of the new web tool, and the code repo for said web tool:

• Spreadsheet version
• Web version (live demo)
• Web version (full code repo)

In the spreadsheet version, column O contains a column of numbers that represent the total number of seconds for each table's row. If you were to insert "1 second" into the very top row of the table, then cell O1 would contain the number 1 (1 second). If you were to input a time duration that results in row 2 showing "1 minute", cell O2 would show "60" (60 seconds). And so on and so forth. In the web version, I created an object called numList to store these numbers (so for example, numList[0] has cell O1's/the first row's number, numList[1] has cell O2's/the second row's number, etc).

I've pretty much taken the formulas used in the spreadsheet (which you can see if you click on the cells) and transferred them to my JavaScript code. However, my code doesn't seem to be working the same.

If you were to input "1 day 0 hours 0 minutes 0 seconds" into the top row of the table in the spreadsheet version, you'd see that the second row shows '0 days 23 hours 45 minutes 36 seconds'. However, if you do this in the web version, the second row instead shows '0 days 23 hours 1425 minutes 85536 seconds'. Clearly this is incorrect, as 'hours' shouldn't be going beyond 23 (which it does if you go to 2 days), and 'minutes' and 'seconds' shouldn't be going beyond 59 (because that's just how time works).

I don't understand why this is happening. In the spreadsheet, the formula for generating the 'seconds' number is this:

O2 - ((F2 * 60 * 60 * 24) + (H2 * 60 * 60) + (J2 * 60))

To break it down:

• O2: The number located in cell O2, which in this example is 85536.
• F2: The number located in the day's cell, which is 0.
• H2: The number located in the hour's cell, which is 23.
• J2: The number located in the minute's cell, which is 45.

So when we insert these numbers into the equation:

85536 - ((0 * 60 * 60 * 24) + (23 * 60 * 60) + (45 * 60))
= 85536 - (0 + 82800 + 2700)
= 85536 - 85500
= 36

The result is 36, which is the number that shows up in the second's column. Simple enough, yes?

However, despite me applying this exact logic in my JavaScript code, the 'seconds' number is 85536, not 36. If you'll recall, this was the number given for the total number of seconds for the time given in row 2 (23 hours, 45 minutes, 36 seconds = 85536 seconds). This leads me to believe that numList[i] (which is O2 in the spreadsheet version) isn't subtracting the rest of the equation for some reason.

Here is the portion of my code that is relevant to this problem (but the entire repo with the full JS code is linked above). The main function in question is the last one in the code (numbersCalc()), but I provided all relevant functions, just in case:

const daysInput = document.getElementById('days-input');
const hoursInput = document.getElementById('hours-input');
const minutesInput = document.getElementById('minutes-input');
const secondsInput = document.getElementById('seconds-input');

daysInput.addEventListener('change', numbers);
hoursInput.addEventListener('change', numbers);
minutesInput.addEventListener('change', numbers);
secondsInput.addEventListener('change', numbers);


// CALCULATE BASE NUMBER (CELL O1 IN SPREADSHEET)

function calculateBase(seconds, minutes, hours, days) {
    // Spreadsheet equation: = L1 + (J1 * 60) + (H1 * 60 * 60) + (F1 * 60 * 60 * 24)
    // L1: seconds
    // J1: minutes
    // H1: hours
    // F1: days

    seconds = Number(secondsInput.value);
    minutes = Number(minutesInput.value);
    hours = Number(hoursInput.value);
    days = Number(daysInput.value);

    const l1 = seconds;
    const j1 = minutes;
    const h1 = hours;
    const f1 = days;
    return (l1 + (j1 * 60) + (h1 * 60 * 60) + (f1 * 60 * 60 * 24));
}


// FORMULA TO CALCULATE REST OF NUMBERS FROM COLUMN 'O' IN SPREADSHEET

function calculateNumbers(num) {
    /* Spreadsheet equation: 
       = IFS(
            (O1 - 60) < 0, 0, 
            (O1 - (O1 * 0.99)) > 60, O1 * 0.99, 
            (O1 - (O1 * 0.99))<= 60, O1 - 60, 
            O1 = 0, 0
         )
    */

    const baseNumber = num;
    let number = 0;

    if((baseNumber - 60) < 0) {
        number = 0;
    } else if ((baseNumber - (baseNumber * 0.99)) > 60) {
        number = baseNumber * 0.99;
    } else if ((baseNumber - (baseNumber * 0.99)) <= 60) {
        number = baseNumber - 60;
    } else if (baseNumber === 0) {
        number = 0;
    }

    return number;
}


// CREATE THE NUMLIST OBJECT, WHICH HOSTS NUMBERS FROM SPREADSHEET'S 'O' COLUMN

let numList = {};

function numbers() {
    numList[0] = calculateBase();

    for(let i = 1; i < 31; i++) {
        numList[i] = Math.round(calculateNumbers(numList[i - 1]));
    }

    numbersCalc();
}


// CALCULATE NUMBERS FOR TABLE'S 'DAYS', 'HOURS', 'MINUTES', & 'SECONDS' CELLS

function numbersCalc() {
    const tds = document.querySelectorAll('td');

    tds.forEach(td => {
        let daysBox = 0;
        let hoursBox = 0;
        let minutesBox = 0;
        let secondsBox = 0;

        for(let i = 0; i < 31; i++) {
            if(td.classList.contains(`days-${i}`)) {
                // Spreadsheet's equation: INT(O2 / (60 * 60 * 24))
                daysBox = (numList[i] / (60 * 60 * 24))
                td.textContent = parseInt(daysBox);

            } else if (td.classList.contains(`hours-${i}`)) {
                // Spreadsheet's equation: INT(O2 - (F2 * 60 * 60 * 24)) / (60 * 60)
                hoursBox = (numList[i] - (daysBox * 60 * 60 * 24)) / (60 * 60);
                td.textContent = parseInt(hoursBox);

            } else if (td.classList.contains(`minutes-${i}`)) {
                // Spreadsheet's equation: INT((O2 - (F2 * 60 * 60 * 24) - (H2 * 60 * 60)) / 60)
                minutesBox = (numList[i] - (daysBox * 60 * 60 * 24) - (hoursBox * 60 * 60)) / 60;
                td.textContent = parseInt(minutesBox);

            } else if (td.classList.contains(`seconds-${i}`)) {
                // Spreadsheet's equation: O2 - ((F2 * 60 * 60 * 24) + (H2 * 60 * 60) + (J2 * 60))
                secondsBox = numList[i] - ((daysBox * 60 * 60 * 24) + (hoursBox * 60 * 60) + (minutesBox * 60));
                td.textContent = parseInt(secondsBox);

            }
        }
    })
}

Answer 1

See comment of jabaa.

I would change the classNames like "days-1" into "days" as the 1 indicates the row number.

I whould loop each tr, init the daysBox, hoursBox and so on.

Then loop each td inside the tr and check for the classNames (without the row number).