Get next working day in online excel by running script

Could some one help me with sharepoint excel script.

I am trying to get next working day form online excel by executing the script.

Conditions: If its holiday or weekend or the time is above 5 PM (all in EST) it should give the next working day in C3 cell.

I tried using macros in local machine but bussiness need it on online excel.

Example screeonshot:

I am expecting the output to be 06/26/24 but getting the out put 06/24/24(Holiday)

function main(workbook: ExcelScript.Workbook) {
    console.log("Script started");
    CheckHolidayOrWeekend(workbook);
    console.log("Script finished");
}

function CheckHolidayOrWeekend(workbook: ExcelScript.Workbook) {
    let sheetName = "Sheet1"; // Change to your sheet name
    let ws: ExcelScript.Worksheet = workbook.getWorksheet(sheetName);

    if (!ws) {
        console.log(`Worksheet with name '${sheetName}' not found`);
        return;
    }
    console.log(`Worksheet '${sheetName}' accessed`);

    // Get the used range of column A
    let range: ExcelScript.Range = ws.getUsedRange().getColumn(1);

    if (!range) {
        console.log("No values found in column A");
        return;
    }

    console.log(`Values in range A:A: ${JSON.stringify(range.getValues())}`);

    let flattenedValues: (string | number | boolean)[] = [].concat(...range.getValues());
    let lastRow: number = flattenedValues.filter(value => Boolean(value)).length;
    console.log(`Last row in column A with data: ${lastRow}`);

    // Get current date and time
    let currentDate: Date = new Date();
    let currentTime: string = `${currentDate.getHours()}:${("0" + currentDate.getMinutes()).slice(-2)}`;
    console.log(`Current date: ${currentDate}, Current time: ${currentTime}`);
    let isHoliday: boolean = false;

    // Function to check if a date is in the holiday list
    const isDateInRange = (date: Date, range: ExcelScript.Range): boolean => {
        let rangeValues: (string | number | boolean)[][] = range.getValues();
        console.log(`Checking range: ${range.getAddress()}`);
        console.log(`Range values: ${JSON.stringify(rangeValues)}`);

        if (!rangeValues || rangeValues.length === 0) {
            return false;
        }
        for (let i = 0; i < rangeValues.length; i++) {
            if (rangeValues[i][0] && new Date(rangeValues[i][0].toString()).toDateString() === date.toDateString()) {
                return true;
            }
        }
        return false;
    };

    // Check if today is a holiday
    let checkRangeSize = 100; // Adjust the chunk size as needed
    for (let i = 1; i <= lastRow; i += checkRangeSize) {
        let endRow = Math.min(i + checkRangeSize - 1, lastRow);
        let holidaysRange = ws.getRange(`A${i}:A${endRow}`);
        if (isDateInRange(currentDate, holidaysRange)) {
            isHoliday = true;
            break;
        }
    }

    // Determine the next working date
    let nextWorkingDate: Date = new Date(currentDate);

    if (isHoliday || currentDate.getDay() === 0 || currentDate.getDay() === 6 || currentTime > "17") {
        do {
            nextWorkingDate.setDate(nextWorkingDate.getDate() + 1);

            // Check if the next day is a holiday
            isHoliday = false;
            for (let i = 1; i <= lastRow; i += checkRangeSize) {
                let endRow = Math.min(i + checkRangeSize - 1, lastRow);
                let holidaysRange = ws.getRange(`A${i}:A${endRow}`);
                if (isDateInRange(nextWorkingDate, holidaysRange)) {
                    isHoliday = true;
                    break;
                }
            }
        } while (isHoliday || nextWorkingDate.getDay() === 0 || nextWorkingDate.getDay() === 6);
    }

    // Format the next working date
    let formattedDate: string = `${("0" + (nextWorkingDate.getMonth() + 1)).slice(-2)}/${("0" + nextWorkingDate.getDate()).slice(-2)}/${nextWorkingDate.getFullYear().toString().slice(-2)}`;
    console.log(`Next working date: ${formattedDate}`);

    // Write the result in cell C3 
    ws.getRange("C3 ").setValue(formattedDate);
    console.log("Result written to C3");
}

    

• The date representations in Excel and JavaScript differ. In Excel, dates are counted as days since January 1, 1900 (dates displayed in cells are actually stored as these serial numbers).
• On the other hand, JavaScript uses milliseconds elapsed since January 1, 1970, as its date reference point.
• GetValues() retrieves the serial numbers corresponding to date cells in Excel. When using rangeValues[i][0].toString(), it simply converts the number to a string (all digits) instead of interpreting it as a date.
• Change the code to get lastRow

    // console.log(`Values in range A:A: ${JSON.stringify(range.getValues())}`);
    // let flattenedValues: (string | number | boolean)[] = [].concat(...range.getValues());
    // let lastRow: number = flattenedValues.filter(value => Boolean(value)).length;
    let lastRow: number = range.getLastCell().getRowIndex() + 1;
    console.log(`Last row in column A with data: ${lastRow}`);

Option 1:

• Utilizing range.getTexts() to obtain the formatted date string displayed in cells

    // Function to check if a date is in the holiday list
    const isDateInRange = (date: Date, range: ExcelScript.Range): boolean => {
        let rangeValues: string[][] = range.getTexts();
        console.log(`Checking range: ${range.getAddress()}`);
        console.log(`Range values: ${JSON.stringify(rangeValues)}`);

        if (!rangeValues || rangeValues.length === 0) {
            return false;
        }
        for (let i = 0; i < rangeValues.length; i++) {
            if (rangeValues[i][0] && new Date(rangeValues[i][0]).toDateString() === date.toDateString()) {
                return true;
            }
        }
        return false;
    };

Option 2:

• Transforming a date into a numeric representation following Excel's date logic

// Function to check if a date is in the holiday list
    const isDateInRange = (date: Date, range: ExcelScript.Range): boolean => {
        let rangeValues: (string | number | boolean)[][] = range.getValues();
        console.log(`Checking range: ${range.getAddress()}`);
        console.log(`Range values: ${JSON.stringify(rangeValues)}`);

        if (!rangeValues || rangeValues.length === 0) {
            return false;
        }
        let converted = 25569.0 + ((date.getTime() - (date.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));
        console.log(Math.floor(converted))
        return rangeValues.some(cellValue => cellValue.includes(Math.floor(converted)));
    };