Weeknum2

function main(workbook: ExcelScript.Workbook) {

  // 👉 Change these if needed

  const tableName = "Table1";

  const dateColumnName = "Date";   // Column with date values

  const weekColumnName = "Week";   // New column to create or overwrite

  const table = workbook.getTable(tableName);

  if (!table) {

    console.log(`Table "${tableName}" not found.`);

    return;

  }

  // Get header row range and column indexes

  const headerRange = table.getHeaderRowRange();

  const headers = headerRange.getValues()[0].map(h => String(h ?? "").trim());

  const dateColIndex = headers.indexOf(dateColumnName);

  if (dateColIndex === -1) {

    console.log(`Date column "${dateColumnName}" not found in table.`);

    return;

  }

  // Ensure week column exists; if not, add it to the end

  let weekColIndex = headers.indexOf(weekColumnName);

  if (weekColIndex === -1) {

    // addColumn expects the 0-based index where to insert; use current column count to append

    table.addColumn(table.getColumnCount(), weekColumnName);

    // refresh headers

    const newHeaders = table.getHeaderRowRange().getValues()[0].map(h => String(h ?? "").trim());

    weekColIndex = newHeaders.indexOf(weekColumnName);

    if (weekColIndex === -1) {

      console.log(`Failed to add column "${weekColumnName}".`);

      return;

    }

  }

  // Read date column values between header and total

  const dateRange = table.getColumn(dateColIndex).getRangeBetweenHeaderAndTotal();

  const dateValues = dateRange.getValues();

  const output: string[][] = [];

  for (let i = 0; i < dateValues.length; i++) {

    const raw = dateValues[i][0];

    let d: Date | null = null;

    // 1) If the cell contains a JS Date-like object (Excel runtime), detect by presence of getTime function

    if (raw !== null && typeof raw === "object" && (raw as any).getTime && typeof (raw as any).getTime === "function") {

      const maybeTime = (raw as any).getTime();

      if (!isNaN(maybeTime)) {

        d = new Date(maybeTime);

      }

    } else {

      // 2) If it's a numeric Excel serial (e.g., 45065.9339) -> convert using Excel epoch 1899-12-30

      const num = Number(raw);

      if (!isNaN(num)) {

        const epoch = new Date(1899, 11, 30); // months are 0-based

        const days = Math.floor(num);

        const frac = num - days;

        const ms = Math.round(frac * 24 * 60 * 60 * 1000);

        d = new Date(epoch.getTime() + days * 24 * 60 * 60 * 1000 + ms);

      } else {

        // 3) Try to parse a date string

        const parsed = new Date(String(raw));

        if (!isNaN(parsed.getTime())) {

          d = parsed;

        }

      }

    }

    if (d) {

      const weekNum = getISOWeekNumber(d);

      const formatted = `W${String(weekNum).padStart(2, "0")} ${d.getFullYear()}`;

      output.push([formatted]);

    } else {

      output.push([""]);

    }

  }

  // Write to week column

  table.getColumn(weekColIndex).getRangeBetweenHeaderAndTotal().setValues(output);

}

/**

 * Get ISO week number (1-53)

 */

function getISOWeekNumber(date: Date): number {

  // Copy date so we don't modify the original

  const d = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate()));

  // Set to nearest Thursday: current date + 4 - current day number (Monday=1..Sunday=7)

  const dayNum = d.getUTCDay() || 7;

  d.setUTCDate(d.getUTCDate() + 4 - dayNum);

  // January 1st of the year

  const yearStart = new Date(Date.UTC(d.getUTCFullYear(), 0, 1));

  // Calculate full weeks to nearest Thursday

  const weekNo = Math.ceil((((d.getTime() - yearStart.getTime()) / 86400000) + 1) / 7);

  return weekNo;

}

```0