Weeknum3

 

function main(workbook: ExcelScript.Workbook) {

  // ====== CONFIGURE THESE ======

  const tableName = "tbl_Auto";           // change to your table name

  const dateColumnName = "Planned start"; // change to the column that contains the date

  const weekColumnName = "Week";          // name of the new column to add/overwrite

  // ============================

  const table = workbook.getTable(tableName);

  if (!table) {

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

    return;

  }

  // ensure date column exists

  try {

    table.getColumnByName(dateColumnName);

  } catch (e) {

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

    return;

  }

  // Add week column if missing

  let weekColumn;

  try {

    weekColumn = table.getColumnByName(weekColumnName);

  } catch (e) {

    table.addColumn(-1, weekColumnName);

    weekColumn = table.getColumnByName(weekColumnName);

  }

  // Read date values between header and total

  const dateColumn = table.getColumnByName(dateColumnName);

  const dateValues = dateColumn.getRangeBetweenHeaderAndTotal().getValues();

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

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

    const raw = dateValues[i][0];

    let d: Date | null = null;

    // 1) If value behaves like a Date object

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

      d = new Date((raw as any).getTime());

    } else {

      // 2) If it's a number (Excel serial like 45065.9339)

      const num = Number(raw);

      if (!isNaN(num)) {

        const epoch = new Date(1899, 11, 30); // Excel epoch

        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 parsing as a string date

        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 results to the week column

  weekColumn.getRangeBetweenHeaderAndTotal().setValues(output);

}

/**

 * Compute ISO week number (1-53)

 */

function getISOWeekNumber(date: Date): number {

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

  const dayNum = d.getUTCDay() || 7; // Monday=1 .. Sunday=7

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

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

  return Math.ceil((((d.getTime() - yearStart.getTime()) / 86400000) + 1) / 7);

}