show a due date using data from multiple columns

Out of curiosity asked Copilot (Smart GPT-5 mode) to translate the above LET formula in TypeScript/OfficeScript. 1st attempt was almost right
1st issue due to TypeScript being very strict on types
2nd issue re. converting Excel dates (numbers) to actual Dates

With the 2 issues fixed we looked at improving the option. Main benefits:
- Formatting of the output is automatic: headers + dates (local)
- Should work according to local settings (dd/mm/yyyy; mm/dd/yyyy; dd.mm.yyyy…)

Overall experience was positive. Only thing to note is it was quite positive saying it's not possible to get system locals (suggested a workaround we tested ==> didn't work). After a bit of research found https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.datetimeformatinfo?view=office-scripts that did the trick

function main(
  workbook: ExcelScript.Workbook,
  SourceTableName: string,
  DestinationCell: string
) {
  const sheet = workbook.getActiveWorksheet();

  try {
    // --- Validate table name ---
    const table = workbook.getTable(SourceTableName);
    if (!table) {
      throw new Error(`Table '${SourceTableName}' not found.`);
    }

    // --- Validate destination cell ---
    let destRange: ExcelScript.Range;
    try {
      destRange = sheet.getRange(DestinationCell);
    } catch {
      throw new Error(`Destination cell '${DestinationCell}' is not a valid Excel address.`);
    }

    // --- Source table body range ---
    const srcRange = table.getRangeBetweenHeaderAndTotal();
    const srcRow = srcRange.getRowIndex();
    const srcCol = srcRange.getColumnIndex();
    const srcRows = srcRange.getRowCount();      // body rows
    const srcCols = srcRange.getColumnCount();   // body columns

    const destRow = destRange.getRowIndex();
    const destCol = destRange.getColumnIndex();

    // --- Overlap check (output sized like body) ---
    const overlap =
      destRow < srcRow + srcRows &&
      destRow + srcRows > srcRow &&
      destCol < srcCol + srcCols &&
      destCol + srcCols > srcCol;

    if (overlap) {
      throw new Error(
        `Destination '${DestinationCell}' overlaps with source table '${SourceTableName}'.`
      );
    }

    // --- Get headers and body ---
    const headers: (string | number | boolean | null)[] =
      table.getHeaderRowRange().getValues()[0];
    const body: (string | number | boolean | null)[][] =
      srcRange.getValues();

    // Pivot and due columns
    const pivotColumns = body.map(row => [row[0], row[1]]);
    const dueColumns = body.map(row => row.slice(2, 7));

    // Always treat Monday as start of week
    const today = new Date();
    const dayOfWeek = today.getDay();         // 0=Sunday, 1=Monday, ..., 6=Saturday
    const diffToMonday = (dayOfWeek + 6) % 7; // 0 if Monday, 1 if Tuesday, ..., 6 if Sunday

    const startWeek = new Date(today);
    startWeek.setHours(0, 0, 0, 0);
    startWeek.setDate(today.getDate() - diffToMonday);

    const endWeek = new Date(startWeek);
    endWeek.setDate(startWeek.getDate() + 6);
    endWeek.setHours(23, 59, 59, 999);

    // Mark due dates within this week
    const dueThisWeek = dueColumns.map(row =>
      row.map(val => isDateInWeek(val, startWeek, endWeek) ? 1 : 0)
    );

    // Hide non‑matching dates
    const hidedOtherDates = dueColumns.map((row, i) =>
      row.map((val, j) => dueThisWeek[i][j] === 1 ? val : "")
    );

    // Row/column keep masks
    const keepRows = dueThisWeek.map(row => row.reduce((a, v) => a + v, 0));
    const keepColumns = [1, 1].concat(
      dueThisWeek[0].map((_, j) =>
        dueThisWeek.some(row => row[j] === 1) ? 1 : 0
      )
    );

    // Build matrix and filter rows/columns, prepend headers
    const toFilter = pivotColumns.map((row, i) => row.concat(hidedOtherDates[i]));
    const filteredRows = toFilter.filter((_, i) => keepRows[i] > 0);
    const finalMatrix = [headers].concat(filteredRows).map(row =>
      row.filter((_, j) => keepColumns[j] === 1)
    );

    // --- Clear destination block (include header row) ---
    const outRows = finalMatrix.length;               // current output rows (header + data)
    const outCols = finalMatrix[0].length;            // current output columns
    const clearRows = Math.max(srcRows + 1, outRows); // body + header
    const clearCols = Math.max(srcCols, outCols);

    const clearBlock = destRange.getResizedRange(clearRows - 1, clearCols - 1);
    clearBlock.clear(ExcelScript.ClearApplyTo.all);

    // --- Write result ---
    const targetRange = destRange.getResizedRange(outRows - 1, outCols - 1);
    targetRange.setValues(finalMatrix);

    // --- Format header row ---
    const headerRow = targetRange.getRow(0);
    headerRow.getFormat().getFill().setColor("#196B24"); // dark green
    headerRow.getFormat().getFont().setColor("#FFFFFF"); // white
    headerRow.getFormat().getFont().setBold(true);

    // --- Detect locale short date pattern ---
    // https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.datetimeformatinfo?view=office-scripts
    const cultureInfo: ExcelScript.CultureInfo = workbook.getApplication().getCultureInfo();
    const systemDateTimeFormat: ExcelScript.DatetimeFormatInfo = cultureInfo.getDatetimeFormat();
    const shortDatePattern: string = systemDateTimeFormat.getShortDatePattern();

    // --- Format dates (skip first 2 columns) ---
    const rowCount = targetRange.getRowCount();
    const colCount = targetRange.getColumnCount();

    // Get the sub‑range: all rows except header (start at row 1),
    // and all columns except the first two (start at col 2)
    if (rowCount > 1 && colCount > 2) {
      const dateRange = targetRange
        .getCell(1, 2)                                // top‑left of the sub‑range
        .getResizedRange(rowCount - 2, colCount - 3); // expand to cover remaining rows/cols

      dateRange.setNumberFormatLocal(shortDatePattern);
    }

  } catch (error) {
    console.log(`Error: ${(error as Error).message}`);
  }
}

// Convert Excel serial number to JS Date
function excelSerialToDate(serial: number): Date {
  const excelEpoch = new Date(1899, 11, 30);
  return new Date(excelEpoch.getTime() + serial * 86400000);
}

// Check if a value is a date within the current week
function isDateInWeek(
  val: string | number | boolean | null,
  startWeek: Date,
  endWeek: Date
): boolean {
  if (typeof val === "number") {
    const d = excelSerialToDate(val);
    return d >= startWeek && d <= endWeek;
  }
  return false;
}

I'm sorry for disrupting your post. Can someone please tell me how to start a post/question of my own? Thank you and again I apologize for coming in here to ask. 

Hi

• If you want something that follows exactly your Cond. Format. rules, VBA is required - not an option with Excel Web (your tag)
• With weeks starting on Monday (as below), 11/17/2025 is next week, not this week. If your week doesn't start on Monday the formula must be adjusted, line:

 startWeek,        today - WEEKDAY(today,2) +1,

With data in Table1 (blue table), in B9 a dynamic array with formula:

=LET(
  source,           TAKE( Table1[#All],, 7 ),
  headers,          TAKE( source, 1),
  body,             DROP( source, 1 ),
  pivotColums,      CHOOSECOLS( body, {1,2} ),
  dueColumns,       CHOOSECOLS( body, {3,4,5,6,7} ),
  today,            TODAY(),
  startWeek,        today - WEEKDAY(today,2) +1,
  endWeek,          startWeek +6,
  dueThisWeek,      (dueColumns >= startWeek ) * (dueColumns <= endWeek),
  hidedOtherDates,  IF( dueThisWeek, dueColumns, ""),
  keepRows,         MMULT(dueThisWeek, SEQUENCE(COLUMNS(dueThisWeek),,,0)),
  keepColumns,      HSTACK( {1,1}, MMULT(SEQUENCE(,ROWS(dueThisWeek),,0), dueThisWeek) ),
  toFilter,         HSTACK( pivotColums, hidedOtherDates ),
  filteredRows,     FILTER( toFilter, keepRows ),
  FILTER( VSTACK( headers, filteredRows ), keepColumns )
)

Corresponding sample avail. at:
https://1drv.ms/x/c/1cd824d35610aacb/ETFnFnBpzqtAsluZRtXKMA0B67yD_JXe36wVA4cVtLlLxw?e=cCi57I