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

Solution

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