excel script to create ISO week

 

function main(workbook: ExcelScript.Workbook) {

  // 👉 Change 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) {

    // No table found

    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) {

    table.addColumn(-1, weekColumnName);

    // refresh headers

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

    weekColIndex = newHeaders.indexOf(weekColumnName);

  }

  // 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 = null;

    // if raw is a JS date already

    if (raw instanceof Date && !isNaN(raw.getTime())) {

      d = raw;

    } else {

      // try convert string/number to Date

      const num = Number(raw);

      if (!isNaN(num)) {

        // Excel serial number (e.g., 45065.93) -> convert to JS Date

        // Excel uses 1899-12-30 as epoch for serial numbers

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

        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 {

        // try parse ISO or other 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 don't modify original

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

  // Set to nearest Thursday: current date + 4 - current day number

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

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

  // January 1 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;

}