Script changing dates to text

I"m trying to make a script to transform a column with dates to text. Normally i would just use the TEXT function but the issue is that the new text values need to replace the old date values. The d...

excel

Office Scripts

SergeiBaklan

Nov 08, 2025

As variant you may replace dates with texts in place, it could be like

function main(workbook: ExcelScript.Workbook) {

// https://techcommunity.microsoft.com/discussions/excelgeneral/script-changing-dates-to-text/4468085

  fnDateToText(workbook.getActiveWorksheet().getRange("E:E").getUsedRange())
  fnDateToText(workbook.getActiveWorksheet().getRange("H:H").getUsedRange())

}

const fnDateToText = (columnRange: ExcelScript.Range) =>
  {
    const converted = columnRange.getValues().map(x => {
      const scalar = x[0]
      let dateAsText: number | string | boolean = null
      if (typeof scalar === "number") {
        const d = new Date(Math.round((scalar - 25569) * 86400 * 1000))
        dateAsText =
          d.getDate().toString().padStart(2, '0') + "/" +
          (d.getMonth() + 1).toString().padStart(2, '0') + "/" +
          d.getFullYear().toString()
      } else { dateAsText = scalar }
      return [dateAsText]
    })

  columnRange.setNumberFormatLocal("@")
  columnRange.setValues(converted)
  
  }

Function converts numbers (which dates actually are) in the column into text with date converted to "dd/mm/yyy". And ignores other values in such column.

Kidd_Ip

MVP

Nov 08, 2025

Try this for replace dates with text:

Inserts helper columns with TEXT() formulas.
Converts them to plain text.
Copies the values back to the original columns.
Deletes the helper columns.

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let table = sheet.getTables()[0]; // Assumes there's only one table on the sheet

  // Add helper columns to the table
  table.addColumn(5, true).getRangeBetweenHeaderAndTotal().setFormulaLocal("=TEXT([@Geboortedatum],\"dd/mm/yyyy\")");
  table.getColumn(5).getRange().setNumberFormatLocal("@");

  table.addColumn(17, true).getRangeBetweenHeaderAndTotal().setFormulaLocal("=TEXT([@[Datum Resultaat]],\"dd/mm/yyyy\")");
  table.getColumn(17).getRange().setNumberFormatLocal("@");

  table.addColumn(19, true).getRangeBetweenHeaderAndTotal().setFormulaLocal("=TEXT([@Startdatum],\"dd/mm/yyyy\")");
  table.getColumn(19).getRange().setNumberFormatLocal("@");

  // Copy values from helper columns back to original columns
  let rowCount = table.getRowCount();
  for (let i = 0; i < rowCount; i++) {
    let geboortedatumText = table.getColumn(5).getDataBodyRange().getCell(i, 0).getText();
    let datumResultaatText = table.getColumn(17).getDataBodyRange().getCell(i, 0).getText();
    let startdatumText = table.getColumn(19).getDataBodyRange().getCell(i, 0).getText();

    table.getColumnByName("Geboortedatum").getDataBodyRange().getCell(i, 0).setValue(geboortedatumText);
    table.getColumnByName("Datum Resultaat").getDataBodyRange().getCell(i, 0).setValue(datumResultaatText);
    table.getColumnByName("Startdatum").getDataBodyRange().getCell(i, 0).setValue(startdatumText);
  }

  // Delete helper columns (assuming they were inserted at fixed positions)
  table.getColumn(19).delete();
  table.getColumn(17).delete();
  table.getColumn(5).delete();
}

dylanvan
Copper Contributor
Nov 10, 2025
Thank you for the aid.
I tried your script but it gave problems with the execution. The script in the other reply worked perfectly so i'm using that one but i wanted to thank you for the aid anyway.

Forum Discussion

Script changing dates to text

Resources