Forum Discussion

Copper Contributor

Nov 07, 2025

Solved

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.

dylanvan

Copper Contributor

Nov 10, 2025

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.

SergeiBaklan

Diamond Contributor

Nov 11, 2025

For few columns we may simplify a bit making one call for all areas

function 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 => fnDateToText(area.getColumn(n).getUsedRange()))
  }
  
}

const dateExcelToUnix = (d: number) => {return new Date(Math.round((d - 25569) * 86400 * 1000))}
const fnDateToText = (columnRange: ExcelScript.Range) =>
  {
    const converted = columnRange.getValues().map(x => {
      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)
  
  }