Script changing dates to text

Question

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

make a new column & use the text funcion to get the new values for all three columns
Change the date notation on the new values to text
copy the new text values to the old column
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

sergeibaklan · Answer

As variant you may replace dates with texts in place, it could be likefunction 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) =&gt;
  {
    const converted = columnRange.getValues().map(x =&gt; {
      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.

dylanvan · Answer

I wanted to thank you. This script works perfectly and even takes other values or empty cells into account. I was going to check those manually but now that's not even necessary.

I had to change the start to

function main(workbook: ExcelScript.Workbook) {

fnDateToText(workbook.getActiveWorksheet().getRange("D:D").getUsedRange())

fnDateToText(workbook.getActiveWorksheet().getRange("O:O").getUsedRange())

fnDateToText(workbook.getActiveWorksheet().getRange("P:P").getUsedRange())

}

Since it's 3 columns but other than that you are a life saver.

kidd_ip · Answer

Try this for replace dates with text:
&nbsp;

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

&nbsp;
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 &lt; 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();
}
&nbsp;

dylanvan · Answer

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.

sergeibaklan · Answer

For few columns we may simplify a bit making one call for all areasfunction main(workbook: ExcelScript.Workbook) {

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

const areas = workbook.getActiveWorksheet().getRanges("D:D,O:P").getAreas()
  for( let area of areas) {
    Array.from(Array(area.getColumnCount()).keys())
      .map(n =&gt; fnDateToText(area.getColumn(n).getUsedRange()))
  }
  
}

const dateExcelToUnix = (d: number) =&gt; {return new Date(Math.round((d - 25569) * 86400 * 1000))}
const fnDateToText = (columnRange: ExcelScript.Range) =&gt;
  {
    const converted = columnRange.getValues().map(x =&gt; {
      const scalar = x[0]
      let dateAsText: number | string | boolean = null
      typeof scalar === "number" ?
        dateAsText = new Intl.DateTimeFormat("en-GB").format(dateExcelToUnix(scalar)) :
        dateAsText = scalar
      return [dateAsText]
    })

columnRange.setNumberFormatLocal("@")
  columnRange.setValues(converted)
  
  }&nbsp;

Forum Discussion

Script changing dates to text

5 Replies

Resources