Forum Discussion

dylanvan's avatar
dylanvan
Copper Contributor
Nov 07, 2025

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 dates are now in three columns with the number notation date and it needs to be a text with the form "dd/mm/yyyy". 

Since i need to do this for at least 50 files i tought about using a script to

  1. make a new column & use the text funcion to get the new values for all three columns
  2. Change the date notation on the new values to text
  3. copy the new text values to the old column
  4. Delete the new column.

The issue i meet is at the second stap that the values are in a table and are not all the same length.

At the moment i have this for first step in a script

function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();

  // Insert at range E:E on selectedSheet, move existing cells right

  selectedSheet.getRange("E:E").insert(ExcelScript.InsertShiftDirection.right);

  // Set range E2 on selectedSheet

  selectedSheet.getRange("E2").setFormula("=TEXT([@Geboortedatum],\"dd/mm/jjjj\")");

  // Insert at range Q:Q on selectedSheet, move existing cells right

  selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right);

  // Set range Q2 on selectedSheet

  selectedSheet.getRange("Q2").setFormula("=TEXT([@[Datum Resultaat]],\"dd/mm/jjjj\")");

  // Set range S2 on selectedSheet

  selectedSheet.getRange("S2").setFormula("=TEXT([@Startdatum],\"dd/mm/jjjj\")");

}

 

And as second step 

function main(workbook: ExcelScript.Workbook) {

  let selectedSheet = workbook.getActiveWorksheet();

  // Set format for range E:E on selectedSheet

  selectedSheet.getRange("E:E").setNumberFormatLocal("@");

  // Set format for range Q:Q on selectedSheet

  selectedSheet.getRange("Q:Q").setNumberFormatLocal("@");

  // Set format for range S:S on selectedSheet

  selectedSheet.getRange("S:S").setNumberFormatLocal("@");

}

 

But i'm stuck at the third step. If there is an easier method that would be fine to

excel
Office Scripts

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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's avatar
    Kidd_Ip
    MVP
    Nov 08, 2025

    Try this for replace dates with text:

     

    1. Inserts helper columns with TEXT() formulas.
    2. Converts them to plain text.
    3. Copies the values back to the original columns.
    4. 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();
}

     

Resources