excel script to create week number

 

function main(workbook: ExcelScript.Workbook) {

  // 👉 Change these two based on your file

  const tableName = "Table1";

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

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

  const table = workbook.getTable(tableName);

  // Try to get week column; if not present, add it

  let weekColumn = table.getColumnByName(weekColumnName);

  if (!weekColumn) {

    table.addColumn(-1, weekColumnName);

    weekColumn = table.getColumnByName(weekColumnName);

  }

  const dateColumn = table.getColumnByName(dateColumnName);

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

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

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

    const d = new Date(dateValues[i][0]);

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

      // Calculate week number (1 to 53)

      const weekNum = getWeekNumber(d);

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

      output.push([formatted]);

    } else {

      output.push([""]);

    }

  }

  weekColumn.getRangeBetweenHeaderAndTotal().setValues(output);

}

/**

 * Normal WEEKNUM logic (Sunday = first day)

 */

function getWeekNumber(date: Date): number {

  const start = new Date(date.getFullYear(), 0, 1);

  const diff = (date.getTime() - start.getTime()) / (1000 * 60 * 60 * 24);

  return Math.ceil((diff + start.getDay() + 1) / 7);

}

-------+++++-----------

function getWeekNumber(date: Date): number {

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

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

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

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

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

}